1

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.

Community
  • 1
  • 1
grenade
  • 31,451
  • 23
  • 97
  • 126

5 Answers5

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
Maximilian Mayerl
  • 11,253
  • 2
  • 33
  • 40
  • 1
    It almost worked. I added a disambiguator between the first "SELECT" and "ID" [eg: "SELECT T.ID"] and a column name after "Max(Revision)" [eg: "Max(Revision) Revision"]. and that does return the correct rows. Have yet to performance test... – grenade Sep 16 '09 at 10:20
1

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

Johannes Rudolph
  • 35,298
  • 14
  • 114
  • 172
1

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.

mas-designs
  • 7,498
  • 1
  • 31
  • 56
Heiko Hatzfeld
  • 3,197
  • 18
  • 15
  • +1 Cheers!, works when I add the disambiguator and appears to be the same solution as Maximilian's. I also like your cheating idea and will probably end up with a variation of that based on the post linked by Johannes Rudolph. – grenade Sep 16 '09 at 10:27
  • Np... It all depends on table usage etc... Also make sure you construct the covering index with the 2 fields as sugested (Reverse sorting of the 2nd field). – Heiko Hatzfeld Sep 16 '09 at 10:55
  • watch out using "old style" joins – KM. Sep 16 '09 at 12:12
  • Sorry.... My last project forced me to use them for ages... I find them horrible myself ;) – Heiko Hatzfeld Sep 16 '09 at 12:31
0

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
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • 1
    Thanks. Your revision returns the right rows but it creates duplicate results. I haven't worked out why. – grenade Sep 16 '09 at 10:23
0

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)
KM.
  • 101,727
  • 34
  • 178
  • 212