August 13, 2008 at 12:45 pm
I have a database containing a table listing the revision history of drawings. Let's say drawing1 has 5 revisions A,B,C,D,and E. I want my query to return only the latest one, in this case "F". What function can I use to do this? I thought the MAX function would work but it doesn't.
Thanks for any help that can be offered.
August 13, 2008 at 12:50 pm
Something along these lines should work:
SELECT *
FROM TableA a
WHERE a.Id = (SELECT TOP(1) a2.Id
FROM TableA a2
ORDER BY a2.Id DESC)
This assumes that the ID values are incremental and that the highest one would be the most recent. It would work the same way with dates or some other increasing value so that you can get the data in the correct order.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2008 at 1:03 pm
Sorry for being such a newbie but I'm having a bit of trouble trying to figure out how to apply your solution to my query. Here is a copy of my query that currently returns all versions of drawings having "CMS" in the drawing number.
SELECT wcadmin.WTDocumentMaster.WTDocumentNumber AS NUMBER, wcadmin.WTdocument.nameB2folderingInfo AS FOLDER,
wcadmin.WTdocument.title AS TITLE, wcadmin.WTdocument.versionIdA2versionInfo AS VERSION,
wcadmin.WTdocument.iterationIdA2iterationInfo AS ITERATION, wcadmin.WTdocument.statestate AS STATE,
wcadmin.ApplicationData.fileName AS 'FILE NAME'
FROM wcadmin.WTDocument INNER JOIN
wcadmin.HolderToContent ON wcadmin.WTdocument.idA2A2 = wcadmin.HolderToContent.idA3A5 INNER JOIN
wcadmin.ApplicationData ON wcadmin.HolderToContent.idA3B5 = wcadmin.ApplicationData.idA2A2 INNER JOIN
wcadmin.FvItem ON wcadmin.ApplicationData.idA3A5 = wcadmin.FvItem.idA2A2 LEFT OUTER JOIN
wcadmin.WTDocumentMaster ON wcadmin.WTdocument.idA3masterReference = wcadmin.WTDocumentMaster.idA2A2
WHERE (wcadmin.WTDocumentMaster.WTDocumentNumber LIKE N'CMS%')
ORDER BY NUMBER, VERSION DESC
How would you change this to work with your solution?
Thanks again.
-Scott
August 13, 2008 at 1:18 pm
If I'm reading this correctly, something like this:
SELECT wcadmin.WTDocumentMaster.WTDocumentNumber AS NUMBER, wcadmin.WTdocument.nameB2folderingInfo AS FOLDER,
wcadmin.WTdocument.title AS TITLE, wcadmin.WTdocument.versionIdA2versionInfo AS VERSION,
wcadmin.WTdocument.iterationIdA2iterationInfo AS ITERATION, wcadmin.WTdocument.statestate AS STATE,
wcadmin.ApplicationData.fileName AS 'FILE NAME'
FROM wcadmin.WTDocument INNER JOIN
wcadmin.HolderToContent ON wcadmin.WTdocument.idA2A2 = wcadmin.HolderToContent.idA3A5 INNER JOIN
wcadmin.ApplicationData ON wcadmin.HolderToContent.idA3B5 = wcadmin.ApplicationData.idA2A2 INNER JOIN
wcadmin.FvItem ON wcadmin.ApplicationData.idA3A5 = wcadmin.FvItem.idA2A2 LEFT OUTER JOIN
wcadmin.WTDocumentMaster ON wcadmin.WTdocument.idA3masterReference = wcadmin.WTDocumentMaster.idA2A2
WHERE (wcadmin.WTDocumentMaster.WTDocumentNumber LIKE N'CMS%')
AND wcadmin.WTDocumentMaster.versionIdA2versionInfo = (SELECT TOP(1) d2.versionIdA2versionInfo
FROM wcadmin.WTDocument d2
WHERE d2.PK = wcadmin.WTDocumentMaster.pk
ORDER BY d2.versionIdA2versionInfo DESC)
ORDER BY NUMBER, VERSION DESC
I'm not sure what your PK is, so I just substituted.
There are a number of other ways to use this. You might look at CROSS APPLY for a start. That could perform the same type of action.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2008 at 1:32 pm
Hey, that was so fast it was scary. I substituted the PK with my values and it seems to work great. Thanks again!
-Scott
August 13, 2008 at 1:56 pm
scott.owen (8/13/2008)
Hey, that was so fast it was scary. I substituted the PK with my values and it seems to work great. Thanks again!-Scott
What else could we expect from The Scary DBA??
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply