TSQL grouping/select help

Hi all wonder if someone can lend a hand; i've got this tsql script (shown below) that is currently returning data based on the owner id, if the record is active and if the record created date is less than todays date. I am then grouping the data together. What i want to achieve is return the most recent record per company.

Currently the data i return is 开发者_JS百科this:

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

This is how i want the data to return:

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

Could someone, point me in the right direction please?

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

---

Try this

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