【PostgreSQL】特定のIDで最大値の枝番号のデータを取得したい

Question

前提・実現したいこと

特定のidで枝番（edaban)が最大値のデータを取得したいです。

発生している問題・エラーメッセージ

SQL文での正しい書き方が分からず、エラーを吐き続けています。
正しいSQLを教えて頂ければ幸いです。

下記のテーブルで、
idが'A1'でedabanが最大値のレコードを取得したいのですが、どのようなSQL文にすれば宜しいでしょうか。
A1,002,BBB,2020-01-25 09:35:43 のようなデータを取得出するのが理想です。
どうぞ宜しくお願い致します。

該当のソースコード

table_master
+----+--------+------------+----------------------+
| id | edaban |   syurui   |        date          | 
+----+--------+------------+----------------------+
| A1 |  001   |     AAA    | 2020-01-24 08:21:01  |
| A1 |  002   |     BBB    | 2020-01-25 09:35:43  |
| B2 |  001   |     BBB    | 2020-01-25 10:51:12  |
| B3 |  001   |     BBB    | 2020-01-25 12:10:50  |
| C1 |  002   |     CCC    | 2020-01-26 16:51:08  |
+----+--------+------------+----------------------+

試したこと

SELECT id, MAX(edaban), syurui, date
FROM table_master
WHERE (id,edaban,shurui,date) 
IN(select id, MAX(edaban),date 
FROM table_master
WHERE(id,shurui) 
IN(SELECT id ='A1', MAX(shurui) 
FROM table_master GROUP BY id)
GROUP BY id,shurui);

Comments

[sazi]

「idが'1'」とはどういう意味ですか？

[koaradosu]

saziさん
Aが抜けておりました...
ご指摘ありがとうございます。

[sazi]

> idが'A1'でedabanが最大値
なら、サンプルデータでは、edabanは'001'ですけど、求めたい結果は'002'になっています。
どのような法則でしょうか？

[koaradosu]

度々のご指摘ありがとうございます。
'001'と'002'の書き間違えです...

[sazi]

質問のデータを修正されたのですね。

Answer 1

取り敢えず、以下の２パターンで、実行計画を確認し、効率の良い方を採用して下さい。
※(id, edaban)のインデックスは最低限必要だと思います。

-- 相関副問合せ
SELECT *
FROM  table_master t1
WHERE id='A1'
  and edaban = (
        select max(edaban) 
        from table_master 
        wheren id=t1.id
      )

-- 副問合せ
SELECT *
FROM  table_master 
WHERE (id, edaban) in (
        select id, max(edaban) 
        from  table_master
        where id='A1'
        group by id
      )

Comments

[koaradosu]

saziさん
ご指摘含め、本当にありがとうございます。
自分で当初やりたいと思ってたのが副問合せの書き方だったので参考になります！

Answer 2

最大の枝番のIDと枝番を取得して、これを本テーブルにJOIN（key：id、edaban）してA1を選んでいます。
枝番はvarcharとしていますが、NUMERIC(3)の方が良いかも

create temp table table_master (id varchar, edaban varchar, syurui varchar, date timestamp,
primary key (id, edaban));
insert into table_master values
( 'A1' , '001'  ,  'AAA'   ,'2020-01-24 08:21:01' ),
( 'A1' , '002'  ,  'BBB'   ,'2020-01-25 09:35:43' ),
( 'B2' , '001'  ,  'BBB'   ,'2020-01-25 10:51:12' ),
( 'B3' , '001'  ,  'BBB'   ,'2020-01-25 12:10:50' ),
( 'C1' , '002'  ,  'CCC'   ,'2020-01-26 16:51:08' );

SELECT m.* FROM table_master m,
(SELECT id, MAX(edaban) as edaban FROM table_master group by id) t
WHERE
m.id=t.id AND m.edaban=t.edaban
AND m.id='A1';

SELECT m.* FROM table_master m
JOIN (SELECT id, MAX(edaban) as edaban FROM table_master group by id) t
USING(id, edaban) WHERE m.id='A1';

Comments

[koaradosu]

amuraさん
ありがとうございます！
無事に理想通りのデータが得られました。
JOIN使ったやり方もあったのですね・・・
勉強になります！