SQL how to get the max date when already using max

I want to get the maximum f.post date, and then the maximum a.process date. When I try to use MAX(a.processdate), it won’t work because of the group by and the other MAX already used.

SELECT f.PARENTACCOUNT
        , MAX(f.POSTDATE) AS [Post Date]
        , a.ProcessDate
        , s.type
        , CONCAT(n.FIRST, ' ', n.MIDDLE, ' ', n.LAST) AS [Member Name]
        , s.BALANCE
FROM dbo.FMHISTORY f
JOIN dbo.ACCOUNT a
    ON f.PARENTACCOUNT = a.ACCOUNTNUMBER
JOIN dbo.NAME n 
    ON f.PARENTACCOUNT = n.PARENTACCOUNT
        AND a.ProcessDate = n.ProcessDate
JOIN dbo.SAVINGS s
    ON f.PARENTACCOUNT = s.PARENTACCOUNT
        AND a.ProcessDate = s.ProcessDate
    where (a.WARNINGCODE1 = 52
        OR a.WARNINGCODE2 = 52
        OR a.WARNINGCODE3 = 52
        OR a.WARNINGCODE4 = 52
        OR a.WARNINGCODE5 = 52
        OR a.WARNINGCODE6 = 52
        OR a.WARNINGCODE7 = 52
        OR a.WARNINGCODE8 = 52
        OR a.WARNINGCODE9 = 52
        OR a.WARNINGCODE10 = 52)
        AND n.TYPE = 0
        AND f.PARENTACCOUNT = '0000123456'
GROUP BY f.PARENTACCOUNT, s.type, s.BALANCE, a.ProcessDate, n.FIRST, n.MIDDLE, n.LAST
ORDER BY f.PARENTACCOUNT

This is my result so far:

PARENTACCOUNT   Post Date       ProcessDate   type      Member Name     BALANCE
0000123456  2020-01-24 00:00:00  20180831      1          Jane Doe      12345.04
0000123456  2020-01-24 00:00:00  20180930      1          Jane Doe      12345.12
0000123456  2020-01-24 00:00:00  20181031      1          Jane Doe      12345.23
0000123456  2020-01-24 00:00:00  20181130      1          Jane Doe      12345.31

I want it to just return this:

PARENTACCOUNT   Post Date       ProcessDate   type      Member Name     BALANCE
0000123456  2020-01-24 00:00:00  20181130      1          Jane Doe      12345.31

Note: a.processdate is an INT (I did not create the database that way).

Answer

Well, you can use select top (1):

select top (1) . . .
. . .
order by f.PARENTACCOUNT, processdate desc

If you need to do this for multiple parent accounts, then a simple method is:

select top (1) with ties . . .
. . .
order by row_number() over (partition by f.parentaccount, processdate desc)

Advertisement

Answer