是否有办法根据其他列中的数据列出事件的最新日期？

Question

我正在编写一个查询，显示每个有大家庭的人在过去一年中的最新工作开始日期（我不应该显示未来日期）。可能有多个家庭（在多个州）在同一天开始工作。在这种情况下，我需要列出州、两个人以及各自的日期。但是，我应该只列出每个州/人对一次。

此外，如果此人在过去一年内没有开始工作，我仍然应该列出此人的姓名，但在州名称的位置，我应该让查询返回NULL，日期返回NULL。

以下是原始表格中的日期：

LOC FAM PPL MILESTONE_ID    MILESTONE_NAME  START_DATE  
WI  Smith   Mike    1   End College 9/4/2017 0:00  
WI  Smith   Mike    2   Start Job   9/4/2017 0:00  
WI  Smith   Bob 1   End College 6/4/2019  
WI  Smith   Bob 2   Start Job   6/4/2019  
IL  Thomas  Mike    1   End College 1/4/2019  
IL  Thomas  Mike    2   Start Job   6/4/2019  
IL  Thomas  Bob 1   End College 12/4/2019  
IL  Thomas  Bob 2   Start Job   6/4/2019  

我知道我需要使用子查询来获取最新的作业开始日期，但我的子查询没有按预期运行。我也尝试过使用CTE，但也不起作用。

这是我到目前为止所拥有的。我还没有让子查询正常工作。我仍然需要添加上面情况的NULL部分

Select family.*     
From 
FAMILY.KEYINFO      as  family     
Inner Join  
(Select family.milestone_id, MAX(family.start_date) as LatestDate  
    from FAMILY.keyinfo  
    group by milestone_id) groupeddate  
    on family.milestone_id=groupeddate.milestone  
    where family.start_date<= CURRENT_TIMESTAMP   
    and family.start_date > DATEADD(year,-1,GETDATE())  

如果查询正确，我希望得到以下答案：

LOC PPL     START_DATE  
N/A Mike    N/A  
N/A Mike    N/A  
WI  Bob 6/4/2019  
IL  Mike    6/4/2019  
IL  Bob 6/4/2019

Answer 1

您似乎想要窗口功能：

select f.*
from (select f.*,
             rank() over (partition by fam order by start_date desc as seqnum
      from families f
      where milestone_name = 'Start Job'
     ) f
where seqnum = 1;