トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
5-40 グループごとに最大値の行を取得
SQLパズル
Table540テーブル
COL1 COL2 COL3 COL4
---- ---- ---- ----
1 1 1 3
2 1 2 6
3 1 3 9
3 2 4 3
3 2 5 6
3 2 6 9
4 3 7 3
Table540テーブルの、Col2のグループごとの最大のCol1を持つ行を出力する。
出力結果
COL1 COL2 COL3 COL4
---- ---- ---- ----
3 1 3 9
3 2 4 3
3 2 5 6
3 2 6 9
4 3 7 3
データ作成スクリプト
create Table Table540(
Col1 number(1),
Col2 number(1),
Col3 number(1),
Col4 number(1));
insert into Table540 values(1,1,1,3);
insert into Table540 values(2,1,2,6);
insert into Table540 values(3,1,3,9);
insert into Table540 values(3,2,4,3);
insert into Table540 values(3,2,5,6);
insert into Table540 values(3,2,6,9);
insert into Table540 values(4,3,7,3);
commit;
SQL
--■■■in述語を使う方法■■■
select Col1,Col2,Col3,Col4 from Table540 a
where (Col1,Col2) in (select max(b.Col1),b.Col2 from Table540 b group by b.Col2)
order by Col2,Col1,Col3;
--■■■existsを使う方法■■■
select Col1,Col2,Col3,Col4 from Table540 a
where not exists(select 1 from Table540 b
where b.Col2 = a.Col2
and b.Col1 > a.Col1)
order by Col2,Col1,Col3;
--■■■分析関数を使う方法■■■
select Col1,Col2,Col3,Col4
from (select Col1,Col2,Col3,Col4,
max(Col1) over(partition by Col2) as MaxCol1
from Table540)
where Col1=MaxCol1
order by Col2,Col1,Col3;
解説
Col2のグループごとのCol1の最大値と等しいことを条件としてます。