0

I have a table with data as follows:

cat score
a   80
c   88
b   36
b   96
d   99
b   76
d   89
a   50
d   69
b   36
d   59
b   96
b   86
c   98
a   50
a   90
c   83
b   66

How can I use SQL to get the max 3 score rows for each cat?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
liu gang
  • 331
  • 3
  • 15
  • Possible duplicate of [Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) – Andrey Korneyev Jun 28 '16 at 13:12
  • I agree that it is a duplicate. I'm not convinced that the thread cited offers the best example. – Strawberry Jun 28 '16 at 13:31
  • Note that you have no PRIMARY KEY - which may prove problematic further down the road. – Strawberry Jun 28 '16 at 13:34

3 Answers3

2

You can use variables for this:

SELECT cat, score
FROM (
  SELECT cat, score,
         @seq := IF(@c = cat, @seq + 1,
                    IF(@c := cat, 1, 1)) AS seq
  FROM mytable
  CROSS JOIN (SELECT @c := '', @seq := 0) x
  ORDER BY cat, score DESC ) AS t
WHERE seq <= 3
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
1

You can use union 

(select cat, score 
from my_table
where cat='a'
order by score desc
limit 3)
union 
(select cat, score 
from my_table
where cat='b'
order by score desc
limit 3)
union 
(select cat, score 
from my_table
where cat='c'
order by score desc
limit 3)
union 
(select cat, score 
from my_table
where cat='d'
order by score desc
limit 3)
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
1

You can do it with a correlated query :

SELECT tt.cat,tt.score FROM (
    SELECT t.cat,t.score,
           (SELECT COUNT(*) FROM YourTable s FROM YourTable s
            WHERE s.cat = t.cat and t.score <= s.score) as cnt
    FROM YourTable t) tt
WHERE tt.cnt < 4
sagi
  • 40,026
  • 6
  • 59
  • 84