sqldb2ibm-midrange

How to join table to itself and select max values in SQL


I have a contracts table:

contractId    date       price     partId
1             20120121   10        1
2             20110130   9         1
3             20130101   15        2
4             20110101   20        2

The contract with greatest date being the active contract (don't blame me, I blame infor for creating xpps)

I need to create query to see only active contracts (one contract per part, the contract with highest date).

So the result of the query should be like this:

contractId    date       price     partId
1             20120121   10        1
3             20130101   15        2

I am out of ideas here, I tried self joining the table, I tried aggregation functions, but I can't figure it out. If anyone would have any idea, please share them with me..


Solution

  • this will work on almost all RDBMs,

    SELECT  a.*
    FROM    tableName A
            INNER JOIN
            (
                SELECT partID, MAX(date) maxDate
                FROM    tableName
                GROUP BY partID
            ) B on a.partID = b.partID AND
                    a.date = b.maxDate
    

    if your RDBMS supports Window Function,

    SELECT  contractId ,date, price,partId
    FROM    
    (
        SELECT contractId ,date, price,partId,
                ROW_NUMBER() OVER (PARTITION BY PartID
                                    ORDER BY date DESC) rn
        FROM    tableName
    ) s
    WHERE   rn = 1