Select latest revision of each row in a table

I have table structures that include a composite primary key of id & revision where both are integers.

I need a query that will return the latest revision of each row. If I understood this answer correctly then the following would have worked on an Oracle DB.

SELECT Id, Title
FROM ( SELECT Id, Revision, MAX(Revision) OVER (PARTITION BY Id) LatestRevision FROM Task )
WHERE Revision = LatestRevision

I am using SQL Server (2005) and need a performant query to do the same.

Answer

See this post by ayende for an ealuation of the Best strategies.

Advertisement

Answer