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.

Answer 1

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.