CODEWITHSUNDEEP
sqlsql-server
P.J. Beauduy
P.J. Beauduy

Reputation: 9

How to select only the most recent

Table A has ID and date and name. Each time the record is changed the first 11 digits of the Id remain the same but the final digit would increase by 1. For example

123456789110 01-01-2020 John smith
119876543210 01-01-2020 Peter Griffin
119876543211 05-01-2020 Peter Griffin

How could I write a statement that shows The iD associated with John smith as well as the most recent Id of Peter Griffin? Thanks

Upvotes: 0

Views: 42

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

Yet another option is using WITH TIES

Select top 1 with ties *
 From  YourTable
 Order by row_number() over (partition by left(id,11) order by date desc)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Why not just use max()?

select name, max(id)
from t
group by name;

Upvotes: 1

Related Questions