0

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

Drew
  • 24,851
  • 10
  • 43
  • 78
ash
  • 781
  • 1
  • 9
  • 20
  • 5
    Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – PM 77-1 Nov 17 '15 at 21:50

3 Answers3

1

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

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • the solution is working in terms of ranking but still not displaying 10 rows as needed. – ash Nov 17 '15 at 22:00
  • @ash There was a small bug in the query, fixed it. Do you want *I have to get top 10 in each category* or *10 rows in total* returned? – Giorgos Betsos Nov 17 '15 at 22:04
  • Yes top 10 in each catory – ash Nov 17 '15 at 22:06
  • somehow the count is not working. I am still getting more than 10 rows. I am unable to share the data as it's more than 100k rows. – ash Nov 17 '15 at 22:10
  • 1
    @ash Of course you get more than 10 rows, that's the real intention, isn't it: *get top 10 in **each** category*? – Giorgos Betsos Nov 17 '15 at 22:11
  • So may be I should have said limit to top 10 in each category – ash Nov 17 '15 at 22:21
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/95389/discussion-between-ash-and-giorgos-betsos). – ash Nov 17 '15 at 22:23
  • sorry man, the code you gave me worked. The dumb me was using the wrong category. Appreciate your help brother. – ash Nov 17 '15 at 22:49
-1

since category appears multiple times, you will need to group by category, summing up the amounts. Then sort on it and keep the number of rows you want.

I think the following will work:

SELECT category, SUM(amount) as amount
FROM mytable
GROUP BY category
ORDER BY SUM(amount) DESC
LIMIT 10
DBug
  • 2,502
  • 1
  • 12
  • 25
  • this wont work as I am not trying to sum up the amount. I want to display top 10 purchase for each category in separate rows. – ash Nov 17 '15 at 21:57
-1

Try this

Select Top 10
      person, category, sum(amount) as Amount
      from Table
      group by person,category
Coder221
  • 1,403
  • 2
  • 19
  • 35