因此，我想采用这组“返回的”结果，并根据创建的日期最短（最新）的日期将其转换为下面的结果，以表示最新添加的结果。
PhoneNum sourcetable FullName reference Task CreatedDate
0 This is not important Mr john smith 39161 This is different every time, but has been renamed 16/03/2015 15:01:05
0 This is not important Mr john smith 39161 This is different every time, but has been renamed 12/03/2015 16:58:22
0 This is not important Mr john smith 39161 This is different every time, but has been renamed 12/03/2015 16:58:25
0 This is not important Mr john smith 39161 This is different every time, but has been renamed 10/03/2015 12:29:50
0 This is not important Mr john smith 39161 This is different every time, but has been renamed 12/03/2015 14:18:47
0 This is not important Mr john smith 39161 This is different every time, but has been renamed 10/03/2015 12:40:21
1 This is not important Mr wilson smith 39158 This is different every time, but has been renamed 10/03/2015 12:07:14
1 This is not important Mr wilson smith 39158 This is different every time, but has been renamed 10/03/2015 12:07:14
1 This is not important Mr wilson smith 39158 This is different every time, but has been renamed 10/03/2015 12:07:13
我需要退货：
PhoneNum sourcetable FullName reference Task CreatedDate
0 This is not important Mr john smith 39161 This is different every time, but has been renamed 12/03/2015 16:58:25
1 This is not important Mr wilson smith 39158 This is different every time, but has been renamed 10/03/2015 12:07:14
到目前为止我有什么
select distinct d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref,t.Subject,t.CreatedDate
from Dial d
join Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
join Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
join Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
join Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID
join Database.dbo.Tasks T on T.FK_ApplicationID = c.FK_ApplicationID
where c.FK_ClientID in (39157,39160)
任何帮助，将不胜感激。
请使用排名功能查找旧记录，未经测试！！希望这个帮助
SELECT * FROM (
select distinct d.PhoneNum,d.sourcetable,N.FullName,C.fk_applicationid as ref,t.Subject,t.CreatedDate
, RANK() OVER ( PARTITION BY N.FullName ORDER BY t.CreatedDate DESC ) AS iRank
from Dial d
join Database.dbo.DM_PhoneNumbers p on p.PhoneNum1 = d.PhoneNum collate latin1_general_CI_AS
join Database.dbo.DM_PhoneNumbers on p.PhoneNum2 = d.PhoneNum collate latin1_general_CI_AS
join Database.dbo.DM_ClientApplicants C on C.FK_ClientID = P.FK_ApplicationID
join Database.dbo.DM_Names N on c.FK_ClientID = N.FK_ApplicationID
join Database.dbo.Tasks T on T.FK_ApplicationID = c.FK_ApplicationID
where c.FK_ClientID in (39157,39160)
) AS t
WHERE t.iRank = 1
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句