How to Select Most Recently Updated Row Based on Sequence ID

Question

I have a primary ID column but also a sequence ID column for each edit of the row (the initial row is 1, each edit increments the sequence ID but the primary ID column remains the same). What is the best way to select only the most up-to-date (max sequence ID) row in queries? I currently do this programmatically but was wondering if there was a way to do this all in one query with SQL.

Sample Data:

Person_ID Person_Seq_ID Person_Name Person_Favorite_Color
--------- ------------- ----------- ---------------------
1 1 John Doe Green
1 2 John Doe Turquoise
2 1 Jane Doe Blue
3 1 Mike Smith Red

Let's suppose I have this query:

SELECT * FROM Person

I would only want the most recent John Doe row to return (sequence ID #2 in this case), and Jane's and Mike's single rows should also be returned.

Desired Results:

Person_ID Person_Seq_ID Person_Name Person_Favorite_Color
--------- ------------- ----------- ---------------------
1 2 John Doe Turquoise
2 1 Jane Doe Blue
3 1 Mike Smith Red

What's the best way to do this with one SQL query so that it can be used with different types of queries such as if I only want to return certain columns?

Answer 1

;WITH x AS
(
 SELECT Person_ID, Person_Seq_ID, Person_Name, Person_Favorite_Color,
 rn = ROW_NUMBER() OVER (PARTITION BY Person_ID ORDER BY Person_Seq_ID DESC)
 FROM dbo.Person -- always use schema prefix
)
SELECT Person_ID, Person_Seq_ID, Person_Name, Person_Favorite_Color
 FROM x WHERE rn = 1
 ORDER BY Person_ID;

Answer 2

Below will also do the trick ... SQLFiddle here :

SELECT [Person_ID]
 ,[Person_Seq_ID]
 ,[Person_Name]
 ,[Person_Favorite_Color]
FROM dbo.Person P1
WHERE P1.[Person_Seq_ID] = (
 SELECT max([Person_Seq_ID]) -- You want the latest sequence_ID
 FROM dbo.Person P2
 WHERE P1.Person_ID = P2.Person_ID
 --AND Person_Name LIKE '%Doe%'
 )
order by [Person_ID]

• Aaron Bertrand: Note that this query will get exponentially more expensive as the size of the table goes up, since it will repeat that correlated subquery for each person...