Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted
;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;
share|improve this answer
add comment

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]
share|improve this answer
1  
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... –  Aaron Bertrand Oct 23 '13 at 20:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.