Display N most sold items per day

Question

I am clueless how can I write a (MySQL) query for this. I am sure it is super simple for an experienced person.

I have a table which summarizes sold items per day, like:

date	item	quantity
2020-01-15	apple	3
2020-01-15	pear	2
2020-01-15	potato	1
2020-01-14	orange	3
2020-01-14	apple	2
2020-01-14	potato	2
2020-01-13	lemon	5
2020-01-13	kiwi	2
2020-01-13	apple	1

I would like to query the N top sellers for every day, grouped by the date DESC, sorted by date and then quantity DESC, for N = 2 the result would look like:

date	item	quantity
2020-01-15	apple	3
2020-01-15	pear	2
2020-01-14	orange	3
2020-01-14	apple	2
2020-01-13	lemon	5
2020-01-13	kiwi	2

Please tell me how can I limit the returned item count per date.

you can find the answer here https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group — Dean Taler, Sep 02 '21 at 05:12

TimLer · Answer 1 · 2021-09-02T06:05:50.603

1

First of all, it is not a good idea to use DATE as the name of a column.

You can use @rank := IF(@current = date, @rank + 1, 1) to number your rows by DATE. This statement checks each time that if the date has changed, it starts counting from zero.

Select date, item, quantity
from
(
     SELECT   item, date, sum(quantity) as quantity,
              @rank := IF(@current = date, @rank + 1, 1) as ranking,  
              @current := date
     FROM     yourtable
     GROUP BY item, date
     order by date, sum(quantity) desc
) t
where t.ranking < 3

edited Sep 02 '21 at 06:05

answered Sep 02 '21 at 05:33

TimLer

1,320
2
16

@sqlnewbie . . . (1) This may look like it works, but MySQL specifically warns against assigning variables and using them in different expressions. (2) Window functions are a much better solution. If you are not using MySQL 8, then your question should be clear on the version you are using. – Gordon Linoff Sep 02 '21 at 11:57

Wynn Teo · Answer 2 · 2021-09-02T07:56:28.363

0

You can do this if you are using MySQL 8.0++

SELECT * FROM 
 (SELECT DATE, ITEM, QUANTITY, ROW_NUMBER() OVER (PARTITION BY DATE ORDER BY QUANTITY DESC) as order_rank FROM TABLE_NAME) as R
WHERE order_rank < 2

edited Sep 02 '21 at 07:56

answered Sep 02 '21 at 05:19

Wynn Teo

170
10

thanks for your time, it is not 100% what I wanted. This query returns a total of N-1 rows, but only 1 per day. So with WHERE order_rank < 5 it would return 4 separate days and only 1 top-seller per day. – sqlnewbie Sep 02 '21 at 07:49
Oppps my bad, miss out the partition. Please see updated answer. – Wynn Teo Sep 02 '21 at 07:56

score 0 · Answer 3 · answered Sep 02 '21 at 12:00

I think you can use:

select t.*
from t
where (quantity, item) >= (select t2.quantity, t2.item
                           from t t2
                           where t2.date = t.date
                           order by t2.quantity desc, t2.item
                           limit 1 offset 1
                          );

The only caveat is that you need to have at least "n" items available on the day (although that condition can be added as well).

Display N most sold items per day

3 Answers3