get the max sequence number of a specific column grouping

Question

As the Title says, How can I select rows having the max sequence number per AssetCode Groupings.

I tried the IN clause operator but it wont take two columns.

AssetCode             SeqNo
---------------------------
AWET-2015-00001         1
AWET-2015-00001         2
AWET-2015-00001         3
AWET-2015-00001         4
AWET-2015-00002         1
AWET-2015-00002         2
AWET-2015-00002         3

Code:

SELECT * 
FROM E_FixedAssetLedger
WHERE Fal_AssetCode, Fal_SeqNo IN (SELECT Fal_AssetCode, MAX(Fal_SeqNo)
                                   FROM E_FixedAssetLedger
                                   GROUP BY Fal_AssetCode)

score 2 · Accepted Answer · answered May 16 '22 at 01:05

We can try to use aggregate function subquery with JOIN to make it.

SELECT f.*
FROM E_FixedAssetLedger f
INNER JOIN (
    SELECT Fal_AssetCode,Max(Fal_SeqNo) Fal_SeqNo
    FROM E_FixedAssetLedger
    GROUP BY Fal_AssetCode
) fa ON f.Fal_AssetCode = fa.Fal_AssetCode
AND f.Fal_SeqNo = fa.Fal_SeqNo

If your RDBMS support window function we can try to use ROW_NUMBER window function to get Max(SeqNo) each AssetCode

SELECT *
FROM (
 SELECT *,
    ROW_NUMBER() OVER(PARTITION BY AssetCode ORDER BY  SeqNo DESC) rn
 FROM E_FixedAssetLedger
) t1
WHERE rn = 1

Wow Thank you so much. I did not thought we can achieve this using `INNER JOIN`. @D-Shih — Its_Me, May 16 '22 at 01:11

get the max sequence number of a specific column grouping

1 Answers1