CODEWITHSUNDEEP
sqlamazon-redshift
user8834780
user8834780

Reputation: 1670

Pick status based on application date

Sample data: 

ID  Goal    Status              applied_at
A   A       Ongoing             1-Jan
A   B       Applied             1-Feb
B   C       Ongoing             1-Mar
B   D       Rebalanced          1-Apr
B   E       Signed Paperwork    1-May

I am trying to pick a status per ID based on min(applied_at). Any suggestions how I can do that?

Expected Output:

ID  Status           
A   Ongoing                    
B   Ongoing

Upvotes: 0

Views: 25

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use corelated subquery

select t.ID,t.status from your_table t where t.applied_at = 
                       ( select min(applied_at) from your_table t1 
                        where t1.ID =t.ID
                        )

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Other option would be to use subquery :

select t.id, t.status
from table t
where applied_at = (select min(t1.applied_at) from table t1 where t1.id = t.id);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

row_number() is a typical approach:

select id, status
from (select t.*, row_number() over (partition by id order by applied_at) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions