Selecting Maximum Version Number from Two Columns

Question

This relates to another question I asked previously. You may have a better understanding of this if you quickly scan it. Version Numbers float, decimal or double

I have two colums and a foreign in a database table. A [Version] column and a [Revision] column. These are in relation to version numbers. e.g. Version 1, Revision 2 = v1.2

What I need to do is grab the maximum version number for a particular foreign key.

Here's what I have so far:

SELECT f.[pkFileID]
   ,x.[fkDocumentHeaderID]
   ,f.[fkDocumentID]
   ,x.[Version]
   ,x.[Revision]
   ,f.[FileURL]
   ,f.[UploadedBy]
   ,f.[UploadedDate]
FROM 
(
     SELECT 
     docs.[fkDocumentHeaderID]
     ,MAX([Version]) AS Version
     ,MAX([Revision]) AS Revision
 FROM 
     [ClinicalGuidanceV2].[dbo].[tbl_DocumentFiles]
 INNER JOIN 
     dbo.tbl_Documents docs ON [fkDocumentID] = [pkDocumentID]
 GROUP BY
     docs.[fkDocumentHeaderID]
)
AS x
INNER JOIN
 dbo.tbl_DocumentFiles f ON 
 f.[fkDocumentHeaderID] = x.[fkDocumentHeaderID] AND 
 f.[Version] = x.[Version] AND
 f.[Revision] = x.[Revision]

Basically grabbing the maximum and joining back to itself. This obvisouly doesn't work because if I have version numbers 1.1, 1.2 and 2.0 the maximum value I'm returning from the above query is 2.2 (which doesn't exist).

What I need to do (I think) is select the maximum [Version] and then select the maximum [Revision] for that [Version] but I can't quite figure how to do this.

Any help, suggestions, questions are all welcome.

Thanks.

score 2 · Accepted Answer · answered Nov 16 '10 at 14:37

You could change it to

SELECT  f.[pkFileID]
        ,x.[fkDocumentHeaderID]
        ,f.[fkDocumentID]
        ,x.[Version]
        ,x.[Revision]
        ,f.[FileURL]
        ,f.[UploadedBy]
        ,f.[UploadedDate]
FROM    (
          SELECT  docs.[fkDocumentHeaderID]
                  ,MAX([Version] * 100000 + [Revision]) AS [VersionRevision] 
          FROM    [ClinicalGuidanceV2].[dbo].[tbl_DocumentFiles]
                  INNER JOIN dbo.tbl_Documents docs 
                    ON [fkDocumentID] = [pkDocumentID]
          GROUP BY
                  docs.[fkDocumentHeaderID]
        )AS x
        INNER JOIN dbo.tbl_DocumentFiles f 
          ON f.[fkDocumentHeaderID] = x.[fkDocumentHeaderID] 
             AND f.[Version] * 100000 + f.[Revision] = x.[VersionRevision]

The idea is to multiply the Version with a constant large enough so it never collides with revision (I have taken 100.000 but any value would do).

After that, your JOIN does the same to retrieve the record.

score 0 · Answer 2 · answered Nov 16 '10 at 14:38

The below should work to extract the top revision.

SELECT TOP 1 f.[pkFileID]
   ,x.[fkDocumentHeaderID]
   ,f.[fkDocumentID]
   ,x.[Version]
   ,x.[Revision]
   ,f.[FileURL]
   ,f.[UploadedBy]
   ,f.[UploadedDate]
FROM 
(
     SELECT 
     docs.[fkDocumentHeaderID]
     ,MAX([Version]) AS Version
     -- Comment this out ,MAX([Revision]) AS Revision
 FROM 
     [ClinicalGuidanceV2].[dbo].[tbl_DocumentFiles]
 INNER JOIN 
     dbo.tbl_Documents docs ON [fkDocumentID] = [pkDocumentID]
 GROUP BY
     docs.[fkDocumentHeaderID]
)
AS x
INNER JOIN
 dbo.tbl_DocumentFiles f ON 
 f.[fkDocumentHeaderID] = x.[fkDocumentHeaderID] AND 
 f.[Version] = x.[Version] 
ORDER BY x.Revision DESC

Namely, it extracts only the records using the max version into table x. Then it orders these records by revision in descending order, and extracts the topmost of the bunch.

This query only returns 1 result, instead of the the top result for all foreign keys. Not quite what I was looking for but thanks for the help. — Jamie, Nov 16 '10 at 15:03

Selecting Maximum Version Number from Two Columns

2 Answers2