I’m trying to get just top 3 selling products grouped within categories (just top 3 products by occurrence in transactions (id) count(id)
by each category). I was searching a lot for possible solution but with no result. It looks like it is a bit tricky in MySQL since one can’t simply use top() function and so on. Sample data structure bellow:
x
+--------+------------+-----------+
| id |category_id | product_id|
+--------+------------+-----------+
| 1 | 10 | 32 |
| 2 | 10 | 34 |
| 3 | 10 | 32 |
| 4 | 10 | 21 |
| 5 | 10 | 100 |
| 6 | 7 | 101 |
| 7 | 7 | 39 |
| 8 | 7 | 41 |
| 9 | 7 | 39 |
+--------+------------+-----------+
Advertisement
Answer
In earlier versions of MySQL, I would recommend using variables:
select cp.*
from (select cp.*,
(@rn := if(@c = category_id, @rn + 1,
if(@c := category_id, 1, 1)
)
) as rn
from (select category_id, product_id, count(*) as cnt
from mytable
group by category_id, product_id
order by category_id, count(*) desc
) cp cross join
(select @c := -1, @rn := 0) params
) cp
where rn <= 3;