Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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?

share|improve this question
1  
What database are you using? The most general approach is to use row_number(), but this is not available on all DBS. – Gordon Linoff May 16 '12 at 13:23
It's MS SQL server 2008, however I'd prefer something more agnostic ideally – RemarkLima May 16 '12 at 13:24
As a caution, all the solutions that use a group by assume that there is only one vers per rec.. Is this the case? Of duplicates, do you want multiple rows or just one? – Gordon Linoff May 16 '12 at 13:35
There's should never be any duplicated "ver" per record ID, as this is incrimented in the application layer... Well that's the plan! – RemarkLima May 16 '12 at 16:26

4 Answers

up vote 4 down vote accepted

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
share|improve this answer
This assumes that there is only one max per recid. – Gordon Linoff May 16 '12 at 13:33
thank you, very speedy response and you just pipped Jason to the post for the answer! – RemarkLima May 16 '12 at 13:38
@GordonLinoff - From the question (selecting "the highest version" for each recId) it seems that there is only one max. – dj18 May 16 '12 at 13:41
@GordonLinoff - True for the none ROW_NUMBER version. Perhaps another reason to prefer ROW_NUMBER. – Lieven Keersmaekers May 16 '12 at 13:43
@dj18 Correct, I'm just after the highest "Ver" per distinct recID! – RemarkLima May 16 '12 at 16:25
up vote 3 down vote

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
share|improve this answer
up vote 1 down vote

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
share|improve this answer
up vote 1 down vote

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.

share|improve this answer
Thank you, this is a great alternateive and I'll check which one is quicker for my needs and setup - a good alternative to the GROUP BY – RemarkLima May 16 '12 at 13:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.