Forum Discussion
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?