Получите новейшую запись, используя часть даты и времени, получая ошибку в агрегатной функции

Я пытаюсь получить самые последние записи со статусом «Зарегистрировано, просрочено», используя значение даты состояния, чтобы получить последнюю.

У меня есть запрос, я использую определенный номер лота, который имеет 13 идентификаторов записей.

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

Я получаю этот список:

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

Вы можете видеть, что самым последним является тот, что вверху LR2014-00519, основываясь на значении временной части STATUS_DATE.

Затем я добавил еще один MAX вокруг столбца RECORD_ID и ORDER BY STATUS_DATE.

поэтому пересмотренный запрос теперь выглядит так:

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

Но когда я запускаю его, я получаю сообщение об ошибке:

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.

Но у меня есть STATUS_DATE в агрегатной функции MAX в SELECT.

Что я делаю не так? Я надеюсь, что кто-то может предложить некоторую помощь, чтобы исправить это.

Спасибо любезно.


sql sql-server aggregate-functions
person erasmo carlos    schedule 28.08.2019    source источник
comment
Дайте вашему столбцу MAX(STATUS_DATE) псевдоним, а затем упорядочите по этому псевдониму.   -  person JMabee    schedule 28.08.2019
comment
Каков ваш ожидаемый результат?   -  person Isaac    schedule 28.08.2019
comment
С решением @JM_ запрос будет завершен, но не вернет ожидаемый результат, как вы получите, для номера лота максимальная дата статуса и максимальный идентификатор записи. Этот результат отличается от возврата идентификатора записи, связанного с датой максимального состояния.   -  person Olivier Depriester    schedule 28.08.2019
comment
В SELECT имя вашего столбца MAX(STATUS_DATE). В вашем ORDER BY это STATUS_DATE. Что вы ожидаете? Как вы ожидаете, что SQL Server узнает, что вы STATUS_DATE в ORDER BY ссылаетесь на MAX(STATUS_DATE) in SELECT`?   -  person Eric    schedule 28.08.2019

Ответы (2)


arrow_upward
0
arrow_downward

Но у меня есть STATUS_DATE в агрегатной функции MAX в SELECT.

Это из-за агрегации, как обычно, STATUS_DATE исчезает при агрегации, в вашем случае он не будет выполнять порядок, если вы не укажете псевдоним MAX(STATUS_DATE) AS STATUS_DATE

Однако я считаю, что ROW_NUMBER() и подзапрос помогут вам в вашем случае, попробуйте следующее:

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
person Shekar Kola    schedule 28.08.2019

arrow_upward
0
arrow_downward

вы можете сделать это несколькими способами:

Использование row_number()

  SELECT *
  FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY P.LOT NBR
                                      ORDER BY STATUS_DATE DESC) as rn
         FROM YourTable) as t
  WHERE t.rn = 1

Или с подзапросом:

  SELECT *
  FROM YourTable t1
  WHERE STATUS_DATE  = (SELECT MAX(t2.STATUS_DATE)
                        FROM YourTable t2
                        WHERE t2.LOT_NBR = t1.LOT_NBR)

Но в обоих случаях вы должны быть осторожны в том, как вы хотите обращаться со связями.

person Juan Carlos Oropeza    schedule 28.08.2019
comment
Я пытаюсь реализовать примеры, которые вы мне показали. Я обновлю вопрос, заставлю ли я его работать или столкнусь с проблемами. Большое спасибо! - person erasmo carlos; 28.08.2019