update_dateの最新を取得したい場合、over (partition by subusername)でまずsubsernameをグルーピングして、その中のMax(date)を取得する。
どうしてグルーピングするかというと、他のsubsernameも入ってくるから。
|
1 |
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; |
|
1 2 3 4 5 |
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が一致するもののみを取得すれば最新が取れる
|
1 |
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の中の最新日時のレコードのみが取得できる
|
1 2 3 |
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 |