×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SELECT TOP 1 of Each of a Group

SELECT TOP 1 of Each of a Group

SELECT TOP 1 of Each of a Group

(OP)
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

RE: SELECT TOP 1 of Each of a Group

If your data looks like this:

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
 
How about:
Select fldFullName, MAX(fldLastModifiedDate) As MaxModDate
From MyTable
Group By fldFullName

---- Andy

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

RE: SELECT TOP 1 of Each of a Group

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:

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 

Tamar

RE: SELECT TOP 1 of Each of a Group

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

CODE -->

; 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

RE: SELECT TOP 1 of Each of a Group

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-a...
https://www.sqlshack.com/track-history-data-change...

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

RE: SELECT TOP 1 of Each of a Group

(OP)
Thank you all. Your suggestions have worked beautifully.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close