update_dateの最新を取得したい場合、over (partition by subusername)でまずsubsernameをグルーピングして、その中のMax(date)を取得する。
どうしてグルーピングするかというと、他のsubsernameも入ってくるから。
select u.*,dh.date update_date ,max(date) over (partition by subusername) AS latest_date from users u LEFT OUTER JOIN user_histories uh ON u.id = uh.user_id;
id | name | description | subusername | state | update_date | latest_date -----+---------------------------------+-------------+---------------+---------+----------------------------+---------------------------- 666 | https://qwerty654321.vamdemic.xyz | memo | qwerty654321 | Running | 2022-01-31 15:40:57.601097 | 2022-02-05 15:40:57.601097 666 | https://qwerty654321.vamdemic.xyz | memo | qwerty654321 | Running | 2022-02-05 15:40:57.601097 | 2022-02-05 15:40:57.601097 666 | https://qwerty654321.vamdemic.xyz | memo | qwerty654321 | Running | 2022-01-01 15:40:57.11112 | 2022-02-05 15:40:57.601097
上記のクエリ結果をwithでtの別名をつけて、update_dateとlatest_dateが一致するもののみを取得すれば最新が取れる
with t as (select u.*,uh.date update_date ,max(date) over (partition by subusername) AS latest_date from users u LEFT OUTER JOIN user_histories uh ON u.id = uh.user_id) select * from t where update_date = latest_date ;
このような感じでsubusernameがqwerty654321の中の最新日時のレコードのみが取得できる
id | name | description | subusername | state | update_date | latest_date -----+---------------------------------+-------------+---------------+---------+----------------------------+---------------------------- 666 | https://qwerty654321.vamdemic.xyz | memo | qwerty654321 | Running | 2022-02-05 15:40:57.601097 | 2022-02-05 15:40:57.601097