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

Answer 2

Getting maximum ver for a given recID is easy. To get the ID, you need to join on a nested query that gets these maximums:

select ID, ver, recID from table x
inner join
    (select max(ver) as ver, recID
     from table
     group by recID) y
on x.ver = y.ver and x.recID = y.recID

Answer 3

You could use a cte with ROW_NUMBER function:

WITH cte AS(
    SELECT ID, Ver, recID
    ,      ROW_NUMBER()OVER(PARTITION BY recID ORDER BY Ver DESC)as RowNum
    FROM data
)
SELECT ID,Ver,recID FROM cte
WHERE RowNum = 1

Answer 4

straighforward example using a subquery:

SELECT  a.*
FROM    tab a
WHERE   ver = (
            SELECT max(ver)
            FROM   tab b
            WHERE  b.recId = a.recId
        )

(Note: this assumes that the combination of (recId, ver) is unique. Typically there would be a primary key or unique constraint on those columns, in that order, and then that index can be used to optimize this query)

This works on almost all RDBMS-es, although the correlated subquery might not be handled very efficiently (depending on RDBMS). SHould work fine in MS SQL 2008 though.