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
Advertisement
Answer
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