Hello, please sign in or register
You are here: Home

The power of user-defined variables and the subquery

Today my colleague was set with an annoying task of .

"Get the top ten products from each year of the last five... oh, and:  you have to do it in one query"

I dispise such statements devoid of reason. Too many cooks - i feel.

MySQL does let us do this. But first i'm not a fan of UNIONs which are obtuse and have to be dynamically created if you wanted to change the number of years or perhaps put into a procedure if you have the luxury - we dont.

So here's my neat little solution that i put forward.

We first set two default variables

SET @year=0,@counter=0;

Then we create a subquery which adds a @counter to each row and sets the @year. Results are ordered by year and then volume of sales. When the year changes we reset the @counter:=0. We then have an outer query with the condition that the counter must be less than 10

Thus

SELECT * FROM
(
  SELECT @counter:=( CASE WHEN @year!=YEAR(date) THEN 1 ELSE @counter+1 END ) AS counter,
        (@year:=YEAR(date)) AS year,
        product_name
    FROM tb_sales t
   ORDER BY year DESC, 
            vol_sold DESC
) AS tmp
WHERE counter <= 10;

Which I thought was worth sharing.

Note: The order of the columns is important, if you reset the @year before using the @year in the CASE condition it will not work at all.

 

Comments

Drew
I tried running this with an additional GROUP BY condition which failed miserably. The counter is evaluated before the GROUP BY, so the counter was a bit of a mess. An alternative is to use a sub query but the counter didn't even work and the onl...
Created 24/12/08
1015maoqiuyun
coach outlet online, http://www.coachoutletonline-store.us.com/
Created 15/10/15
oakleysunglasses
“I canada goose outlet think it doesn’t swarovski jewelry bode very
Created 21/01/16
linpingping
The michael--kors.org.uk next ralph lauren outlet day patriots jersey Mrs
Created 01/03/16
clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
clibin
Tyrion 5c cases watched her ray ban sunglasses read. His timber...
Created 18/04/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
chenyan
Bond grinned. pandora-bracciali.it "We bcbgmax.in.net only chaussure...
Created 15/06/16
ZZZZ
michael kors handbags nike trainers
Created 05/10/16
cc
adidas yeezy boost Cheap Jordans For Sale tiffany and co jewe...
Created 26/10/16
michael kors factory outlet
adidas nmd pandora bracelet
Created 16/05/17
Title*
Comment

Prove you are not a robot

To prove you are not a robot, please type in the six character code you see in the picture below
Security confirmation codeI can't see this!
Contact
Name*
Email never shown*
Home Page

Author

Andrew Dodson
Since:Feb 2007

Comment | flag

Categories

Bookmark and Share