How to Select Most Recently Updated Row Based on Sequence ID 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?
;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;