TSQL分组/选择帮助

Question

大家好，不知道有没有人能帮个忙;我有这个TSQL脚本(如下所示)，如果记录是活动的，并且如果记录创建的日期小于今天的日期，则该脚本当前根据所有者id返回数据。然后我将数据分组在一起。我想要实现的是返回每个公司最近的记录。

当前i返回的数据是:

COMPANY A   JOE BLOGS   NULL    10088   Green   NULL    NULL    21/07/2007 16:57 Phone Call
COMPANY B   JOE BLOGS   NULL    10059   Green   NULL    NULL    20/07/2007 14:57 Phone Call
COMPANY B   JOE BLOGS   NULL    10059   Green   NULL    NULL    18/07/2006 09:47    E-mail
COMPANY B   JOE BLOGS   NULL    10059   Green   NULL    NULL    19/07/2006 13:19    E-mail
COMAPANY C  JOE BLOGS   NULL    10866   Green   NULL    NULL    17/08/2007 12:57 Phone Call
COMAPANY C  JOE BLOGS   NULL    10866   Green   NULL    NULL    13/08/2007 10:59    E-mail
COMAPANY C  JOE BLOGS   NULL    10866   Green   NULL    NULL    15/08/2007 14:57    E-mail

这就是我想要的数据返回方式:

COMPANY A   JOE BLOGS   NULL    10088   Green   NULL    NULL    21/07/2007 16:57 Phone Call
COMPANY B   JOE BLOGS   NULL    10059   Green   NULL    NULL    20/07/2007 14:57 Phone Call
COMAPANY C  JOE BLOGS   NULL    10866   Green   NULL    NULL    17/08/2007 12:57 Phone Call

谁能告诉我正确的方向?

SELECT fa.name, fa.owneridname, fa.new_technicalaccountmanageridname, fa.new_customerid, fa.new_riskstatusname, 
fa.new_numberofopencases, fa.new_numberofurgentopencases, fap.actualend, fap.activitytypecodename, fap.createdby, fap.createdbyname
FROM FilteredAccount fa
INNER JOIN FilteredActivityPointer fap ON fa.accountid = fap.regardingobjectid
WHERE fa.statecodename = 'Active' 
    AND fap.ownerid = '0F995BDC'
    AND fap.createdon < getdate()
GROUP BY fa.name, fa.owneridname, fa.new_technicalaccountmanageridname, fa.new_customerid, fa.new_riskstatusname, 
fa.new_numberofopencases, fa.new_numberofurgentopencases, fap.actualend, fap.activitytypecodename, fap.createdby, fap.createdbyname

Answer 1

试试这个

SELECT * FROM (
SELECT fa.name, fa.owneridname, fa.new_technicalaccountmanageridname, fa.new_customerid, fa.new_riskstatusname,  
fa.new_numberofopencases, fa.new_numberofurgentopencases, fap.actualend, fap.activitytypecodename, fap.createdby, fap.createdbyname ,
RN = ROW_NUMBER() OVER (PARTITION BY fa.name ORDER BY fap.createdby DESC)
FROM FilteredAccount fa 
INNER JOIN FilteredActivityPointer fap ON fa.accountid = fap.regardingobjectid 
WHERE fa.statecodename = 'Active'  
    AND fap.ownerid = '0F995BDC' 
    AND fap.createdon < getdate() 
) a WHERE RN = 1