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?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Clockwork-Muse, Aug 11 '20 at 21:11

score 2 · Answer 1 · answered Aug 11 '20 at 04:50

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;

score 0 · Accepted Answer · answered Aug 13 '20 at 01:02

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';

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

2 Answers2