CODEWITHSUNDEEP
sql
1131
1131

Reputation: 407

select value based on other columns

I am quite stuck here. I have this table:

  Name  |  Value   |  Date    
------------------------------   
  A     |  Active  | 2020-08-07    
  A     | Inactive | 2020-08-06    
  A     |  Active  | 2020-08-05   
  B     | Inactive | 2020-08-06    
  B     |  Active  | 2020-08-01    
  C     |  Active  | 2020-08-02

And I want to select only the latest records for each name (based on date)

  Name  |  Value   |  Date    
------------------------------    
  A     |  Active  | 2020-08-07    
  B     | Inactive | 2020-08-06    
  C     |  Active  | 2020-08-02

Could you please help me?

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

A simple method uses a correlated subquery:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.name = t.name);

With an index on (name, date), this often has really good performance.

Another method that works well on many databases is row_number():

select t.*
from (select t.*, row_number() over (partition by name order by date desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions