How to get latest data for a column when using grouping in postgres-CodePudding

I am using postgres alongside sequelize. I have encountered a case where I need to write a coustom query which groups the records are a particular field. I know for the remaning columns that are not used for grouping, I need to use a aggregate function like SUM. But the problem is that for some columns I need to get the one what is the latest one (DESC sorted by created_at). I see no function in sql to do so. Is my only option to write subqueries or is there a better way? Thanks?

For better understanding, If you look at the below picture, I want the group the records with address. So after the query there should only be two records, one with sydney and the other with new york. But when it comes to the distance, I want the result of the query to contain the distance form the row that was most recently created, i.e with the latest created_at.

so the final two query results should be:

sydney   100   2022-09-05 18:14:53.492131 05:45
new york  40     2022-09-05 18:14:46.23328 05:45

CodePudding user response：

select address, distance, created_at
from(
        select address, distance, created_at, row_number() over(partition by address order by created_at DESC) as rn
        from table) x
    where rn = 1