sql - 根据申请日期选择状态

Question

样本数据：

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

我正在status尝试ID基于min(applied_at). 有什么建议我该怎么做？

预期输出：

ID  Status           
A   Ongoing                    
B   Ongoing  

标签： sqlamazon-redshift

Answer 1

row_number()是一种典型的做法：

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