Get the newest record using date and time portion, getting error on aggregate function

I am trying to get the most recent record(s) with a status of Registered, Expired, using the status date value to get the latest one.

The query I have , I am using a specific lot number that has 13 record ids to it.

SELECT
    MAX(STATUS_DATE)
,   LOT_NBR
,   RECORD_ID
FROM
    PERMIT P
INNER JOIN LOT L
    ON P.SERV_CODE = L.SERV_CODE
    AND P.ID1 = L.ID1
    AND P.ID2 = L.ID2
    AND P.ID3 = L.ID3
WHERE   
    L.STATUS IN ('Registered', 'Expired')   
    AND P.LOT NBR = '070826204235'
GROUP BY
    RECORD_ID
,   L.LOT_NBR

I get this list:

STATUS_DATE             LOT_NBR         RECORD_ID
2018-12-28 10:11:32.887 070826204235    LR2014-00519
2018-12-28 09:53:52.400 070826204235    LR2016-01722
2018-12-28 09:44:52.487 070826204235    LR2016-01737
2018-12-28 09:44:07.440 070826204235    LR2016-01738
2018-12-28 09:43:19.263 070826204235    LR2016-01739
2018-12-28 09:42:39.313 070826204235    LR2016-01742
2018-12-28 09:39:42.513 070826204235    LR2016-01743
2018-12-28 09:38:46.527 070826204235    LR2016-01744
2018-12-28 09:37:57.007 070826204235    LR2016-01745
2018-12-28 09:35:47.300 070826204235    LR2016-01746
2018-12-28 09:34:41.737 070826204235    LR2016-01747
2018-12-28 09:20:30.663 070826204235    LR2016-01754
2018-12-28 09:19:13.900 070826204235    LR2016-01755

You can see that the most recent is the one at the top LR2014-00519, basing that on the value of STATUS_DATE time portion.

I then added another MAX around the RECORD_ID column, and an ORDER BY STATUS_DATE

so the revised query now looks like:

SELECT 
    MAX(RECORD_ID)
,   MAX(STATUS_DATE)
,   LOT_NBR
FROM
    P WITH(NOLOCK)
INNER JOIN LOT L WITH(NOLOCK)
    ON P.SERV_CODE = L.SERV_CODE
    AND P.ID1 = L.ID1
    AND P.ID2 = L.ID2
    AND P.ID3 = L.ID3
WHERE   
    STATUS IN ('Registered', 'Expired')
    AND L.LOT_NBR = '070826204235'
GROUP BY
    L.LOT_NBR
ORDER BY
    STATUS_DATE

But when I run it I get an error that reads:

Column "STATUS_DATE" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

But I have the STATUS_DATE in an aggregate function MAX within the SELECT.

What am I doing wrong? I hope someone can offer some help to correct this.

Thank you kindly.

Answer

But I have the STATUS_DATE in an aggregate function MAX within the SELECT.

This due to the aggregation, as usual the STATUS_DATE disappears upon aggregation, in your case it wouldn’t do the order by unless you specify alias MAX(STATUS_DATE) AS STATUS_DATE

However, I believe, ROW_NUMBER() and Sub query would help you in your case, try following:

SELECT * 
FROM 
        (SELECT *,  ROW_NUMBER () OVER (PARTITION BY L.LOT_NBR ORDER BY STATUS_DATE DESC) RecentRN
        FROM     PERMIT P WITH(NOLOCK)
                    INNER JOIN LOT L WITH(NOLOCK)
                        ON P.SERV_CODE = L.SERV_CODE  AND P.ID1 = L.ID1  AND P.ID2 = L.ID2  AND P.ID3 = L.ID3
        WHERE   
            STATUS IN ('Registered', 'Expired') --AND L.LOT_NBR = '070826204235'
        ) as SQ
WHERE SQ.RecentRN = 1