SELECT TOP 1 of Each of a Group

[grnzbra]

I have a table in which people have more than one record (they tend to be duplicates) and need to select only one record for each person. This could be done with a cursor but I believe there is a way to phrase a SELECT statement that will give me the desired result. The fields involved are fldFullName and fldLastModifiedDate. I would like to select the record with the latest fldLastModifiedDate value. How can this be done with a SELECT statement

 

[Andrzejek]

If your data looks like this:

[pre]
fldFullName fldLastModifiedDate
grnzbra 1/1/2021
grnzbra 2/2/2022
grnzbra 3/3/2023
Andy 1/1/2021
Andy 2/2/2022
Andy 3/3/2023
[/pre]
How about:[tt]
Select fldFullName, MAX(fldLastModifiedDate) As MaxModDate
From MyTable
Group By fldFullName[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TamarGranor]

Andy's answer is correct, but it's important to realize that you can't just add other fields from the same table to that query and expect them to come from the record with the latest date. If you're looking for other fields as well, the easiest way to do it is to use the OVER clause with the FIRST_VALUE or LAST_VALUE function.

For example, if you have a PayRate field in that table and you want to return the current pay rate for a person, you'd write something like:

[pre]Select fldFullName,
MAX(fldLastModifiedDate) OVER (PARTITION BY fldFullName) As MaxModDate,
FIRST_VALUE(PayRate) OVER (PARTITION BY fldFullName
ORDER BY fldLastModifiedDate DESC) AS CurPayRate
From MyTable[/pre]

Tamar

 

[Chris Miller]

Tricky, but true.

Another I think easier to get solution is to take Andys query to determine the correct pairs of (fldFullName,fldLastModifiedDate) to filter the full records of that table by these pairs.

In a CTE

; With latestpersons as
Select fldFullName, MAX(fldLastModifiedDate) As MaxModDate
From Persons
Group By fldFullName

Select persons.* form persons p
inner join latestpersons lp on lp.fldFullName = p.fldFullName and lp.MaxModDate = p.fldLastModifiedDate

Chriss

 

[Chris Miller]

The better advice is to split up data and move older records in a table for history of records, it's really bad data management to have an insert when an update would do and even if you argue for it that the old states of records are valuable, that can be stored separately.

There are even several mechanisms for that, now. CDC, capture data change, and temporal tables.

https://www.sqlshack.com/change-data-capture-for-auditing-sql-server/

https://www.sqlshack.com/track-hist...server-2016-system-versioned-temporal-tables/

If you implement one or the other, the actual persons table can have latest data only and you don't need to go through any hoops to get only the records you actually need.

I'd recommend temporal tables, as it offers specific queries to get data as it was at a specified datetimne using the AS OF clause, which is also mentioned and explained in the second link about that feature.

Chriss

 

[grnzbra]

Thank you all. Your suggestions have worked beautifully.