How do I find the unique column value with maximum date consideration in PostgreSQL?-CodePudding

For Example

product_id | costhead_id | date
1020209    | 1000105     | 2020-04-01 08:58:15
1020209    | 1000182     | 2021-11-24 11:27:40
1020209    | 1000183     | 2021-12-19 16:19:55
1020210    | 1000105     | 2020-04-01 08:58:15
1020210    | 1000182     | 2021-11-24 11:27:40
1020210    | 1000183     | 2021-12-20 16:19:55

I need a unique product_id with a maximum date. Required value like below

product_id | costhead_id | date
1020209    | 1000183     | 2021-12-19 16:19:55
1020210    | 1000183     | 2021-12-20 16:19:55

CodePudding user response：

You can use distinct on:

select distinct on(product_id) *
from table_name
order by product_id, date desc

Fiddle