ash ash - 4 months ago 17
MySQL Question

top 10 by category mysql

I have a table where I have to get top 10 in each category based on the amount spent by a person in that category. My sample data is

person category amount
roger perfume 20
jim perfume 50
joe tv 5
jim tv 25
kathy car 40
alicia perfume 100


I gotta get this in one query. I know limit does not work on amount like limit amount 10. Please help

Answer

Use variables:

SELECT person, category, amount
FROM (
  SELECT person, category, amount,
         @rn := IF(@cat = category, @rn+1,
                   IF(@cat := category, 1, 1)) AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @cat := '') AS vars
  ORDER BY category, amount DESC) AS t
WHERE t.rn <= 10

@rn is used in order to enumerate rows within each category slice by descending amount order.

Hence, the outer query returns the 10 highest ranking rows per category. If less than 10 records exist for a specific category, then all of these records are returned.

Demo here