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

Question

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..

I am sorry for that mistake, i am a little bit tired sitting whole day in front of telnet emulator trying to figure out this querry.. I am using the DB/2 on IBM AS/400. — Jakub Konop
– Jakub Konop, Commented Nov 29, 2012 at 17:08
Based on the tags, the dbms is officially "DB2 for i". (The system is currently known as IBM PowerSystem running IBM i. They haven't sold an AS/400 since 2000.) — WarrenT
– WarrenT, Commented Nov 29, 2012 at 21:55

John Woo · Accepted Answer · 2012-11-29 17:04:00Z

19

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

SQLFiddle Demo

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

SQLFiddle Demo

answered Nov 29, 2012 at 17:04

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dugas · Accepted Answer · 2023-11-20 15:00:56Z

5

This is the fast self join:

SELECT c1.*  FROM contracts c1   
LEFT OUTER JOIN contracts c2 ON c2.partId = c1.partId AND c1.date < c2.date 
WHERE c2.contractId IS NULL

The use of sub selects (nested selects) tend to be slower.

edited Nov 20, 2023 at 15:00

dugas

12.6k3 gold badges48 silver badges54 bronze badges

answered Jun 27, 2022 at 11:32

Sindre

3206 silver badges8 bronze badges

Comments

Rich.Carpenter · Accepted Answer · 2012-11-29 17:07:21Z

3

SELECT c.*
FROM contracts c
    INNER JOIN 
    (
        SELECT partId, MAX([date]) AS MaxDate
        FROM contracts
        GROUP BY partID
    ) MaxDate
        ON c.partId = MaxDate.partID
            AND c.[date] = MaxDate.[date]

answered Nov 29, 2012 at 17:07

Rich.Carpenter

1,0661 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related