Get rows based on the MAX value of one of the columns in Db2 SQL

Question

I want to get a row based on the MAX value of one of its columns in Db2 SQL.

TABLE_1

ID   ORG    DEST    AccountNumber   Amount  Status   
----------------------------------------------------
11   1224   6778    32345678        458.00  Accepted 
12   1225   6779    12345678        958.00  Rejected 
 4   1226   6780    22345678        478.00  Rejected 
 6   1227   6781    21345678        408.00  Accepted 

TABLE_2

ID     NAME         VERSION
---------------------------
1224   BankA        1
1224   BankA1       2
1225   BankB        1
1226   BankC        1
1227   BankD        1
1227   BankD1       2
6778   TestBankA    1
6778   TestBankA1   2
6778   TestBankA1   3
6779   TestBankB    1
6779   TestBankB1   2
6779   TestBankB2   3
6779   TestBankB3   4
6780   TestBankC    1
6781   TestBankD    1


Expected Output

ID  AccountNumber   Amount  Status    Origin   Destination
----------------------------------------------------------
11  32345678        458.00  Accepted  BankA1   TestBankA1
12  12345678        958.00  Rejected  BankB    TestBankB3
4   22345678        478.00  Rejected  BankC    TestBankC
6   21345678        408.00  Accepted  BankD1   TestBankD

The query below does not show the bank name for the latest version.

SELECT * 
FROM TABLE_1 AS T1
INNER JOIN (SELECT ID, MAX(VERSION)  FROM TABLE GROUP BY ID) AS T2
ON T2.ID = T1.ORG
INNER JOIN (SELECT ID, MAX(VERSION)  FROM TABLE GROUP BY ID) AS T3
ON T3.ID = T1.DEST
WHERE Status <> 'Failed'

Does this answer your question? Select first row in each GROUP BY group? — Clockwork-Muse
– Clockwork-Muse, Commented Aug 11, 2020 at 21:11

Tim Biegeleisen · Accepted Answer · 2020-08-11 04:50:42Z

2

The ROW_NUMBER analytic function provides one option here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VERSION DESC) rn
    FROM TABLE_2
)

SELECT
    t1.AccountNumber,
    t1.Amount,
    t1.Status,
    t2org.NAME AS Origin,
    t2dest.NAME AS Destination
FROM TABLE_1 t1
LEFT JOIN cte t2org
    ON t2org.ID = t1.ORG AND t2org.rn = 1
LEFT JOIN cte t2dest
    ON t2dest.ID = t1.DEST AND t2dest.rn = 1;

answered Aug 11, 2020 at 4:50

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Fred Sobotka · Accepted Answer · 2020-08-13 01:02:30Z

0

Tim's option of using a CTE and the ROW_NUMBER() OLAP function is a good approach.

Since you only want a single column (NAME) from TABLE_2, you could also retrieve it from a correlated subquery, although it might not perform as well if there are lots of qualifying rows in TABLE_1.

SELECT t1.ID, t1.AccountNumber, t1.Amount, t1.Status,
    (SELECT t2r.NAME FROM TABLE_2 AS t2r 
     WHERE t2r.ID = t1.ORG 
     ORDER BY t2r.VERSION DESC FETCH FIRST ROW ONLY
    ) AS Origin,
    (SELECT t2d.NAME FROM TABLE_2 AS t2d 
     WHERE t2d.ID = t1.DEST
     ORDER BY t2d.VERSION DESC FETCH FIRST ROW ONLY
    ) AS Destination
FROM TABLE_1 AS t1
WHERE t1.Status <> 'Failed';

answered Aug 13, 2020 at 1:02

Fred Sobotka

5,33225 silver badges34 bronze badges

Collectives™ on Stack Overflow

Get rows based on the MAX value of one of the columns in Db2 SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related