Select latest revision of each row in a table

Question

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 1

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

Answer 2

I would try to create a subquery like this:

SELECT Id, Title FROM Task T, (Select ID, Max(Revision) MaxRev from Task group by ID) LatestT WHERE T.Revision = LatestT.MaxRev and T.ID = LatestT.ID

Another option is to "cheat" and create a trigger that will flag the revision as latest revision if one item is added. Then add that field to the index. (I would link the table to insert only)

Also an index on ID,Revision desc could help the performance

Answer 3

I think this should work (I didn't test it)...

SELECT      ID, 
            Title
FROM        Task AS T
INNER JOIN
(
    SELECT         ID, 
                    Max(Revision)
    FROM            Task
    GROUP BY        ID
) AS sub
ON          T.ID = sub.ID
AND         T.Revision = sub.Revision

Answer 4

The query you posted will work in SQL 2005 (in compatibility mode 90) with the syntax errors corrected:

SELECT t1.Id, t1.Title
FROM ( SELECT Id, Revision, MAX(Revision) OVER (PARTITION BY Id) LatestRevision FROM Task ) AS x
JOIN Task as t1
ON   t1.Revision = x.LatestRevision
AND  t1.id       = x.id

Answer 5

try this:

DECLARE @YourTable table(RowID int, Revision int, Title varchar(10))
INSERT INTO @YourTable VALUES (1,1,'A')
INSERT INTO @YourTable VALUES (2,1,'B')
INSERT INTO @YourTable VALUES (2,2,'BB')
INSERT INTO @YourTable VALUES (3,1,'C')
INSERT INTO @YourTable VALUES (4,1,'D')
INSERT INTO @YourTable VALUES (1,2,'AA')
INSERT INTO @YourTable VALUES (2,3,'BBB')
INSERT INTO @YourTable VALUES (5,1,'E')
INSERT INTO @YourTable VALUES (5,2,'EE')
INSERT INTO @YourTable VALUES (4,2,'DD')
INSERT INTO @YourTable VALUES (4,3,'DDD')
INSERT INTO @YourTable VALUES (6,1,'F')

;WITH YourTableRank AS
(
SELECT
    RowID,Revision,Title, ROW_NUMBER() OVER(PARTITION BY RowID ORDER BY RowID,Revision DESC) AS Rank
    FROM @YourTable
)
SELECT
    RowID, Revision, Title
    FROM YourTableRank
    WHERE Rank=1

OUTPUT:

RowID       Revision    Title
----------- ----------- ----------
1           2           AA
2           3           BBB
3           1           C
4           3           DDD
5           2           EE
6           1           F

(6 row(s) affected)