liu gang liu gang - 5 months ago 8
SQL Question

sql get the max 3 score rows for each category

I have a database 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?

Answer

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
Comments