I need a sql query to group by name but return oth

问题:

I'm querying a single table called PhoneCallNotes. The caller FirstName, LastName and DOB are recorded for each call as well as many other fields including a unique ID for the call (PhoneNoteID) but no unique ID for the caller. My requirement is to return a list of callers with duplicates removed along with the PhoneNoteID, etc from their most recent entry. I can get the list of users I want using a Group By on name, DOB and Max(CreatedOn) but how do I include uniqueID (of the most recent entry in the results?)

select  O.CallerFName,O.CallerLName,O.CallerDOB,Max(O.CreatedOn)
from [dbo].[PhoneCallNotes] as O
where O.CallerLName like 'Public'
group by O.CallerFName,O.CallerLName,O.CallerDOB order by Max(O.CreatedOn) 

Results:

John    Public  4/4/2001    4/6/12 16:42
Joe     Public  4/12/1988   4/6/12 16:52
John    Public  1/2/1950    4/6/12 17:01

Thanks

回答1:

Assuming that the set of [FirstName, LastName, DateOfBirth] are unique (#shudder#), I believe the following should work, on pretty much every major RDBMS:

SELECT a.callerFName, a.callerLName, a.callerDOB, a.createdOn, a.phoneNoteId
FROM phoneCallNotes as a
LEFT JOIN phoneCallNotes as b
ON b.callerFName = a.callerFName
AND b.callerLName = a.callerLName
AND b.callerDOB = a.callerDOB
AND b.createdOn > a.createdOn
WHERE a.callerLName LIKE 'Public'
AND b.phoneNoteId IS NULL

Basically, the query is looking for every phone-call-note for a particular name/dob combination, where there is not a more-recent row (b is null). If you have two rows with the same create time, you'll get duplicate rows, though.

回答2:

You can also write what Andrey wrote somewhat more compactly if you select TOP (1) WITH TIES and put the ROW_NUMBER() expression in the ORDER BY clause:

SELECT TOP (1) WITH TIES
  CallerFName,
  CallerLName,
  CallerDOB,
  CreatedOn,
  PhoneNoteID
FROM [dbo].[PhoneCallNotes]
WHERE CallerLName = 'Public' 
ORDER BY ROW_NUMBER() OVER(
  PARTITION BY CallerFName, CallerLName, CallerDOB
  ORDER BY CreatedOn DESC
)

(By the way, there's no reason to use LIKE for a simple string comparison.)

回答3:

Try something like that:

;WITH CTE AS (
    SELECT  
        O.CallerFName,
        O.CallerLName,
        O.CallerDOB,
        O.CreatedOn,
        PhoneNoteID,
        ROW_NUMBER() OVER(PARTITION BY O.CallerFName, O.CallerLName, O.CallerDOB ORDER BY O.CreatedOn DESC) AS rn
    FROM [dbo].[PhoneCallNotes] AS O 
    WHERE 
        O.CallerLName LIKE 'Public' 

)
SELECT 
    CallerFName,
    CallerLName,
    CallerDOB,
    CreatedOn,
    PhoneNoteID
FROM CTE
WHERE rn = 1
ORDER BY 
    CreatedOn