CODEWITHSUNDEEP
sqlsql-server
Baked Inhalf
Baked Inhalf

Reputation: 3735

SQL get latest record from inside a resultset, T-SQL

I want to retrieve the latest contract, with the latest revision. The inner query works, but unfortunately I need to figure out a way make it work when it's inside another query...

This works:

SELECT * FROM contract t1
WHERE revisionnum = (select max(revisionnum) 
                     from contract t2 
                     where t1.contractnum = t2.contractnum)`

Problem is that it's inside a select * from contract where statement, so it looks like this:

SELECT * 
FROM contract 
WHERE -- this line cannot be changed!
exists -- not sure about this
(   SELECT * FROM contract t1
    WHERE revisionnum = (select max(revisionnum) 
                         from contract t2 
                         where t1.contractnum = t2.contractnum) 
)

How can I do this?

Upvotes: 0

Views: 111

Answers (2)

JamieD77
JamieD77

Reputation: 13949

I've seen someone use something like this before..

SELECT * 
FROM contract 
WHERE 1 = (Select Count(*) 
           From contract c1 
           Where c1.contractnum = contract.contractnum And c1.revisionnum >= contract.revisionnum)

EXISTS option

SELECT  *
FROM    [contract]
WHERE   EXISTS ( SELECT c1.contractnum,
                        MAX(c1.revisionnum)
                 FROM   [contract] c1
                 WHERE  c1.contractnum = [contract].contractnum
                 GROUP BY c1.contractnum
                 HAVING MAX(c1.revisionnum) = [contract].revisionnum )

Your original query was also pretty close.. you just missed a small filter and aliasing your field names to specify which table you were pointing to.

SELECT  *
FROM    contract
WHERE   EXISTS -- not sure about this
            ( SELECT    *
                FROM    contract t1
                WHERE   contract.contractnum = t1.contractnum -- filter missing
                        AND contract.revisionnum = (SELECT  MAX  (t2.revisionnum) -- missing table specifier
                                                    FROM    contract t2 
                                                    WHERE t1.contractnum = t2.contractnum) 
            )

Upvotes: 1

rtakhar
rtakhar

Reputation: 13

SELECT IDENT_CURRENT([TABLE_NAME]) AS returnID

Upvotes: 0

Related Questions