SQL 並び替えた上で最新のデータを複数取り出したい

Question

お世話になります

重複する項目のある複数のデータが入ったテーブルから、条件を満たすレコードのうち、重複を避けつつ、各々の最新のものだけを取り出すSQLを考えています。

単純な構成の具体例を示すと次のとおりです。

create table test ( id int, name char(4), msg char(4), suji int);

insert into test values(11, ’TARO’, ‘wwww’, 4);
insert into test values(11, ’TARO’, ‘xxxx’, 3);
insert into test values(11, ’HANA’, ‘’yyyy’, 4);
insert into test values(11, ’HANA’, ‘’zzzz’, 3);
insert into test values(10, ’TARO’, ‘ssss’, 2);
insert into test values(10, ’TARO’, ‘tttt’, 1);
insert into test values(10, ’HANA’, ‘’uuuu’, 2);
insert into test values(10, ’HANA’, ‘’zzzz’, 1);

このとき、idが11でかつ、sujiが最も大きいものを、TAROとHANAそれぞれで1つずつ取得したいです。(この例だと「11, ‘TARO’ ,wwww’」と「11, ‘HANA’ ‘yyyy’」の2レコード取れれば良い)

sujiは文法を確認するためのサンプルで、実際にはsujiに日付が入り、最も新しい日付のレコードをSELECTすることを想定しています。また、これはさらにこのあと10と11の差分をみてcase文で条件分岐するような処理も加わるとても大きなSQLの一部です。

たとえばselect * from ( select * from test where id = 11) a;
とすることでidが11の4レコードまで絞り込むことができるのはわかりますが、ここから絞り込めなくて困っています。

SQLにこだわらずVBAやPython等を介せば自力でも解決できるのですが、クライアントの要望としてSQLでデータをを成形して取り出したいようです。

GROUP BYとORDER BYの組み合わせで実現できないだろうかと考えていますが、プロジェクトの都合でひとりで考え込む時間があまりなく、現場でSQLの読み書きができるのが自分だけということで、相談できる人がいません。どうかお力をお貸しください。

postgreSQLを使用しています。よろしくお願いします。

Comments

[kumataroinutaro]

補足です。このサンプルではTAROとHANAのsujiはともに4ですが、同じものが入るとは限りません。各々のsujiの最新を取得したいです。

Answer 1

window関数を使って出来ます。

SELECT id, name, msg, suji
  FROM (SELECT *
             , MAX(suji) OVER (PARTITION BY name) AS max_suji
          FROM test
         WHERE id = 11) as t
 WHERE suji = max_suji

参考までにサブクエリ部分の結果は

id	name	msg	suji	max_suji
11	HANA	yyyy	4	4
11	HANA	zzzz	3	4
11	TARO	wwww	4	4
11	TARO	xxxx	3	4

Answer 2

nameごとにということでしたね・・・これでどうでしょう？

select * from test as t1
where not exists(
select 1 from test where id=t1.id and suji>t1.suji and name=t1.name)
and id=11