CODEWITHSUNDEEP
postgresql
Yogus
Yogus

Reputation: 2272

How to get the latest record on the basis of latest date?

I have a table

**r_id**        **start_date**                   *Status*               
"149165"       "2020-09-07 08:12:46.108"         ongoing           
"149165"       "2020-09-07 08:18:15.934"         resolved 
"149161"       "2020-09-07 08:11:33.923"         ongoing
"149161"       "2020-09-07 08:12:19.887"         resolved 
"149161"       "2020-09-07 08:11:04.448"         ongoing       
"149161"       "2020-09-07 08:09:17.211"         ongoing

Expected output : To add another column latestStat on the basis of latest status on the basis of start_date among group by r_id 

**r_id**        **start_date**              *Status*      **latestStat**         
"149165"       "2020-09-07 08:12:46.108"     ongoing        resolved   
"149165"       "2020-09-07 08:18:15.934"     resolved       resolved
"149161"       "2020-09-07 08:11:33.923"     ongoing        stable
"149161"       "2020-09-07 08:12:19.887"     stable         stable
"149161"       "2020-09-07 08:11:04.448"     ongoing        stable   
"149161"       "2020-09-07 08:09:17.211"     ongoing        stable

Upvotes: 0

Views: 280

Answers (1)

Tometzky
Tometzky

Reputation: 23890

select
  r_id,
  start_date,
  status,
  (
    select status
    from the_table as last_statuses
    where last_statuses.r_id=the_table.r_id
    order by start_date desc
    limit 1
  ) as lateststat
from the_table;

This would benefit from index on the_table(r_id, start_date desc). Alternatively an index on the_table(r_id, start_date) would be sufficient and maybe useful for other queries, but slightly slower, as it had to be scanned backwards. But don't have both.

Upvotes: 1

Related Questions