INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

SELECT TOP 1 to return multiple records

SELECT TOP 1 to return multiple records

(OP)
I have a table of people that has a unique primary key for each record. However, due to radical differences in written language, people can appear several times with different spellings of the same name. There is another foreign key field for which each person has a unique value even though the person has more than one record. I cannot use the primary key.

What I need to do is select the most recent record for each person based on the foreign key and the date that the record was modified. A TOP 1 clause would return just one record. What I need is a select statement that returns the TOP 1 for each person in the table. I could do this with a cursor, but this is already inside of a cursor - I'd like to avoid nested cursors.

I vaguely remember seeing a solution for this (perhaps even here) but that was a long time ago.

Does anyone know how this is done?

RE: SELECT TOP 1 to return multiple records

Could you present a sample of your data, something like:

PK_Field   FK_Field   LastName   Date_Mod
1          22         Brown      1/1/2001
2          23         Brownie    5/5/2005
3          34         Brwney     6/6/2006
4          78         XYZ        9/9/2008
 
and what outcome you would expect based on what rule(s)? (Which records with different spellings would make the same last name?)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: SELECT TOP 1 to return multiple records

OK, I don't know, if I got a correct understanding, but I designed simple sample data about two persons each having 2 entries in a names (history) table.

For the query to pick the latest name entered for each person it's essential the enteredAt datetime differs, so I cheated creating sample data with GetDate()+X. Computers are too fast today, so successive calls of GetDate() might change in split seconds somewhere at decimal places 5 or 6, but before the precision of the datetime type.

That aside here's sample code and the CTE to use:

CODE

Declare @names as table (nameID int identity(1,1), name nchar(50), personID int, EnteredAt datetime);

Insert Into @names values 
('Grean Zebra' ,1,getdate())  ,('Green Zebra' ,1,getdate()+1),
('Olaf Doshkey',2,getdate()+2),('Olaf Doschke',2,getdate()+3);

with latestnamerecords as(
select personID, Max(EnteredAt) as LatestNameAt from @names Group By personID)

Select n.* from @names n inner join latestnamerecords lnr
on n.personID = lnr.personID AND n.EnteredAt = lnr.LatestNameAt
order by personID 

Obviously the CTE query could also be written as a simpler sub query, but I think it makes things better readable and understandable. First step is to pick out max(enteredAt) dates per personID, which is a foreign key to a not implemented persons table holding other non moving data of a person, perhaps. Anyway, all entered/modified versions of names are in the @names table, and the row with the max(enteredAt) date per personID is the latest name record of that person. So the whole row of the max(enteredAt) date per personID is of interest. And that's what the final query retrieves with an inner join of the latestnamerecords via matching both personID and the datetime.

By the way, SQL 2016 has a feature you will like about such historical data: temporal tables, for an introduction see https://www.mssqltips.com/sqlservertip/3680/introd...

Besides some other things it introduces some new query clauses like querying data AS OF a certain datetime, back historically or of course also usable AS OF NOW via specifying AS OF GetDate().

Bye, Olaf.

RE: SELECT TOP 1 to return multiple records

Visually:



The result of latest names (above) in comparison with all names (below). Only rows 2 and 4 are picked, as needed.

Bye, Olaf.

RE: SELECT TOP 1 to return multiple records

Olaf,
grnzbra said: "people can appear several times with different spellings of the same name"
Your example shows people with the same spellings of the names.
That's why I asked for example of data and the rules, especially what makes "different spellings of the same name" - the 'same name'.... ponder

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: SELECT TOP 1 to return multiple records

Look closer, the names change from wrong to correct spellings.

Grean -> Green
Doshkey -> Doschke

Bye, Olaf.

RE: SELECT TOP 1 to return multiple records

Oh, I see.
You then assumed "each person has a unique value even though the person has more than one record" with your personID, and I hope you are right.

"each person has a unique value" could also be interpreted as: sad
nameID  Name  PersonID
1               17
2               19
3               27
4               33 
That's why I always ask for an example of data and don't assume anything, but that's just me smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: SELECT TOP 1 to return multiple records

It would be really nice to see sample data to give a solution fitting the actual data. But since it's about the idea to get a top 1 record per group of records, you can take it from there and adapt it to another situation, too.

The idea of temporal tables I pointed to has an even stricter outset, it asks of two datetime2 columns, a starttime and endtime of the validity of a record, but it separates the current records in one major table and all previous records in a history table and thus getting the latest data is even simpler than querying AS OF NOW, you simply query the data from the main table.

So, whenever you have the chance to go for SQL2016, grnzbra, you should go for that feature, as it's really about the topic of keeping all data history and still have an easy way to act on current data. The nice thing about this feature compared to an older history keeping with CDC (change data capture) is, that it's a feature even going into the free Express version of MSSQL 2016. And on top of that, the history table is maintained from the system automatically, even if you alter the main table. So this surely will be a goal to migrate any self rolled history data keeping done via triggers, if not CDC, so far. It has some weak spots, but especially querying this data is much easier and should outperform every self done history mechanisms especially in getting data AS OF a certain datetime on the past.

I did something like that myself in about 2010 and had a T_data for current data and H_data for historical states. To get the row as of some datetime I did SELECT * FROM t_data UNION SELECT * FROM h_data HAVING adatetime >= validfromDatetTime AND adatetime < validtoDateTime, which can of course be optimized via indexes on these datetime columns, but it's nevertheless a not ideal brute force attack on the problem.

Bye, Olaf.

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!

Resources

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