SQLについて久々に書こうかなと思います。グループ単位で上位N件を取得するSQLですが、使うシチュエーションとしては各教科ごとに成績上位5名ずつ表示する等。まず、
| id | category | point | name |
| 1 | 1 | 100 | foo1 |
| 2 | 1 | 50 | foo2 |
| 3 | 1 | 70 | foo3 |
| 4 | 2 | 30 | bar1 |
| 5 | 2 | 50 | bar2 |
| 6 | 2 | 80 | bar3 |
| 7 | 2 | 10 | foo4 |
| 8 | 3 | 30 | foo5 |
| 9 | 3 | 40 | foo6 |
| 10 | 3 | 50 | bar4 |
| 11 | 3 | 60 | bar5 |
というようなテーブルがあり、各カテゴリ単位でポイント上位2件ずつ取得したいとする場合のSQL。
select
*
from
Table1 as t1
where
id in(select
id
from
Table1 as t2
where
t1.category = t2.category
order by
point desc,
id
limit 2)
order by
category,
point desc
実行結果は以下の通り。
| id |
category |
point |
name |
| 1 |
1 |
100 |
foo1 |
| 3 |
1 |
70 |
foo3 |
| 6 |
2 |
80 |
bar3 |
| 5 |
2 |
50 |
bar2 |
| 11 |
3 |
60 |
bar5 |
| 10 |
3 |
50 |
bar5 |
ちなみに、PostgreSQL8で確認しましたが、他のRDBでは確認していません。このSQLでは同点の場合にID順で上位を決めています。同点は全て出すというルールになると試していないですが、in句の中のサブクエリのwhere句にポイント上位2件を取得して、ポイント同士でくっつける必要があるでしょう。（明日でも試してみようかな・・・）