How can I construction a SQL query to pick the top three (3) items in a group?
I have a list of sports teams which are grouped into divisions, say A,
B, C, D, etc. At the end of the season I would like to get a list of
the top three teams (those with the most wins) in each division. If I
wanted the best team from each division, I could write this:
select div, team, max(wins) from teams group by div ;
Unfortunately, there's no option to max to specify more than one item,
e.g. max(wins,3) to specify the top 3.
In pseudocode, I want to do something similar to this:
for $i in (select div from teams) {
select div, team, wins from teams where div=$i order by wins desc limit 3 ;
}
Is there a way to do the equivalent using only SQL?
Thanks in advance for any pointers.
Regards,
- Robert
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
