SQL Server 2012，选择两个最大记录，然后加入

Question

我试图进行使用常见的ScheduledObjectID字段的多表连接。我想从基于最大RunID和UserDueDate的表中选择多个记录。任何帮助将不胜感激。

的样本数据：

ScheduledObjectID RunID UserDueDate 
------------------------------------------------- 
123    1  2017-07-16 14:26:56.263 
123    2  2017-05-16 14:26:56.250 
123    3  2017-05-16 14:26:56.233 
456    9  2017-05-16 14:26:56.217 
456    10  2017-05-16 14:26:56.200 
456    11  2017-10-07 10:19:33.873 

希望加入数据

ScheduledObjectID RunID UserDueDate 
------------------------------------------------- 
123    3  2017-05-16 14:26:56.233 
456    11  2017-10-07 10:19:33.873 

Answer 1

无需加盟，您可以用顶部1与领带

demo here

select top 1 with ties ScheduledObjectID, RunID, UserDueDate 
from 
#tmp 
order by 
row_number() over (Partition by ScheduledObjectID order by runid desc) 

按照评论这应该工作：

select * from t1 join 
(select ScheduledObjectID,max(runid) as runid,max(userduedate) as userduedate 
from t2 

组由ScheduledObjectID ）上t1.somecol1 = t2.somecol T2