How to select only recently updated records in PostgreSQL-CodePudding

Have a table in postgres(called ledger) that keeps data about some keywords with a structure like below:

 - id
 - keyword
 - updatedAt
 - createdAt
 - ...other details

A single keyword may be in multiple rows, want a query such that it will return only the recent record to a keyword. In other words, will want to select all from ledger but with each keyword appearing once(only the very recent update)

CodePudding user response：

Use distinct on.

You did not mention what is the initial value of the updatedat column. Assuming it is null:

select distinct on (keyword) 
    keyword, 
    coalesce(updatedat, createdat) as last_change_time, 
    other_column
from ledger
order by keyword, last_change_time desc