Search code examples
sqlpostgresqlknex.js

How to select only recently updated records in PostgreSQL

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)

Solution

  • 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