SQL Select highest values from table on two (or more) columns

Question

not sure if there's an elegant way to acheive this:

Data

ID   Ver   recID  (loads more columns of stuff)  
1     1       1  
2     2       1  
3     3       1  
4     1       2  
5     1       3  
6     2       3  

So, we have ID as the Primary Key, the Ver as the version and recID as a record ID (an arbitary base ID to tie all the versions together).

So I'd like to select from the following data, rows 3, 4 and 6. i.e. the highest version for a given record ID.

Is there a way to do this with one SQL query? Or would I need to do a SELECT DISTINCT on the record ID, then a seperate query to get the highest value? Or pull the lot into the application and filter from there?

Answer 1

A GROUP BY would be sufficient to get each maximum version for every recID.

SELECT  Ver = MAX(Ver), recID
FROM    YourTable
GROUP BY
        recID

If you also need the corresponding ID, you can wrap this into a subselect

SELECT  yt.*
FROM    Yourtable yt
        INNER JOIN (
          SELECT  Ver = MAX(Ver), recID
          FROM    YourTable
          GROUP BY
                  recID
        ) ytm ON ytm.Ver = yt.Ver AND ytm.recID = yt.RecID

or, depending on the SQL Server version you are using, use ROW_NUMBER

SELECT  *
FROM    (
          SELECT  ID, Ver, recID
                  , rn = ROW_NUMBER() OVER (PARTITION BY recID ORDER BY Ver DESC)
          FROM    YourTable
        ) yt
WHERE   yt.rn = 1