Search code examples
sqlpostgresqldatetimegreatest-n-per-group

How to fetch the latest records in PostgreSQL?

Actually I am trying to get the latest record of these data with group by r_id and latest start_date

My sample data :

**r_id**   **s_id **     **start_date**                               
"149165"    "28317"   "2020-09-07 08:12:46.108"                      
"149165"    "28324"   "2020-09-07 08:18:15.934"                     
"149161"    "28313"   "2020-09-07 08:11:33.923"      
"149161"    "28316"   "2020-09-07 08:12:19.887"                  
"149161"    "28312"   "2020-09-07 08:11:04.448"                  
"149161"    "28305"   "2020-09-07 08:09:17.211"

Expected results :

    **r_id**   **s_id **     **start_date**                                
    "149165"    "28324"   "2020-09-07 08:18:15.934"                  
    "149161"    "28316"   "2020-09-07 08:12:19.887"

Anyone pls.

Solution

  • You can use distinct on:

    select distinct on (r_id) t.*
from mytable t
order by r_id, start_date desc