【postgres】group by 〜 first row 集計して最初の行をとる
select * from detail;
product_id | item | value
------------+------+--------------
000001 | 01 | 1111-1111-11
000001 | 02 | 100000
000001 | 03 | 100000
000001 | 04 | in
000001 | 05 | 100000
000002 | 01 | 2222-1111-11
000002 | 02 | 100000
000002 | 03 | 100000
000002 | 04 | out
000002 | 05 | 100000
000003 | 01 | 3333-1111-11
000003 | 02 | 100000
000003 | 03 | 100000
000003 | 04 | in
000003 | 05 | 100000
(15 rows)
select *,rank() over (partition by product_id
order by item desc ) as rank
from detail;
product_id | item | value | rank
------------+------+--------------+------
000001 | 05 | 100000 | 1
000001 | 04 | in | 2
000001 | 03 | 100000 | 3
000001 | 02 | 100000 | 4
000001 | 01 | 1111-1111-11 | 5
000002 | 05 | 100000 | 1
000002 | 04 | out | 2
000002 | 03 | 100000 | 3
000002 | 02 | 100000 | 4
000002 | 01 | 2222-1111-11 | 5
000003 | 05 | 100000 | 1
000003 | 04 | in | 2
000003 | 03 | 100000 | 3
000003 | 02 | 100000 | 4
000003 | 01 | 3333-1111-11 | 5
(15 rows)
あとはrank=1のものに条件を絞ればOK