Forum Discussion

TSobiech's avatar
TSobiech
Copper Contributor
Apr 10, 2024

Selecting MAX value in 1 column based on code in another column

Here is a simplified sample of my data... (there are additional columns, but not relevant right now.)

What I need is a SELECT statement that will always return the MAX "RBDTYR" (year) for each RBDTTC, for each RBDCTL

Here's what I have, so far...

SELECT RBDTYR, RBDCTL, RBDTTC
FROM XXXRE60P AS XXXRE60P_1
WHERE (RBDTYR =
(SELECT MAX(RBDTYR) AS Expr1
FROM XXXRE60P AS XXXRE60P_1)) AND (RBDTTC = 100)

Which returns (good, so far)...

 

This WORKS for RBDTTC = 100, and RBDTTC = 200, because all instances of those codes have been updated to 2024.
However, it returns NO records if  RBDTTC = 300 because there are no "2024" records for that code.

What I need are these records for RBDTTC = 300...

 

I could always just manually specify the RBDTYR and RBDTTC...

SELECT TOP RBDTYR, RBDCTL, RBDTTC
FROM XXXRE60P AS XXXRE60P_1
WHERE (RBDTYR = 2023) AND (RBDTTC = 300)

Which returns basically what I need...

 

 

However,  I'd rather not have to manually update the "RBDTYR" value every year.
How do I SELECT in such a way that it will return the MAX value RBDTYR row for each RBDTTC and RBDCTL?



No RepliesBe the first to reply

Resources