Select row in group with largest value in particular column postgres

Question

I have a database table which looks like this.

 id       account_id      action             time_point

 3        234            delete                100
 1        656            create                600
 1        4435           update                900
 3        645            create                50

I need to group this table by id and select particular row where time_point has a largest value.

Result table should look like this:

 id       account_id      action             time_point

 3        234            delete                100
 1        4435           update                900

Thanks for help, qwew

Answer 1

In Postgres, I would recommend distinct on to solve this top 1 per group problem:

select distinct on (id) *
from mytable
order by id, time_point desc

However, this does not allow possible to ties. If so, rank() is a better solution:

select *
from (
    select t.*, rank() over(partition by id order by time_point desc) rn
    from mytable t
) t
where rn = 1

Or, if you are running Postgres 13:

select *
from mytable t
order by rank() over(partition by id order by time_point desc)
fetch first row with ties