Dapatkan catatan terbaru menggunakan bagian tanggal dan waktu, dapatkan kesalahan pada fungsi agregat

Saya mencoba mendapatkan catatan terbaru dengan status Terdaftar, Kedaluwarsa, menggunakan nilai tanggal status untuk mendapatkan yang terbaru.

Permintaan yang saya miliki, saya menggunakan nomor lot tertentu yang memiliki 13 id catatan.

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

Saya mendapatkan daftar ini:

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

Anda dapat melihat bahwa yang terbaru adalah yang ada di LR2014-00519 teratas, berdasarkan nilai porsi waktu STATUS_DATE.

Saya kemudian menambahkan MAX lagi di sekitar kolom RECORD_ID, dan ORDER BY STATUS_DATE

jadi kueri yang direvisi sekarang terlihat seperti:

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

Namun ketika saya menjalankannya saya mendapatkan error yang berbunyi:

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.

Tapi saya memiliki STATUS_DATE dalam fungsi agregat MAX dalam SELECT.

Apa yang saya lakukan salah? Saya harap seseorang dapat menawarkan bantuan untuk memperbaikinya.

Terima kasih.


sql sql-server aggregate-functions
person erasmo carlos    schedule 28.08.2019    source sumber
comment
Berikan kolom Anda MAX(STATUS_DATE) sebuah alias lalu urutkan berdasarkan nama alias tersebut.   -  person JMabee    schedule 28.08.2019
comment
Apa hasil yang Anda harapkan?   -  person Isaac    schedule 28.08.2019
comment
Dengan solusi @JM_, kueri akan selesai tetapi tidak akan mengembalikan hasil yang diharapkan seperti yang Anda dapatkan, untuk nomor lot tanggal status maksimal dan id rekaman maksimal. Hasil ini berbeda dengan mengembalikan id rekaman terkait tanggal status maksimal.   -  person Olivier Depriester    schedule 28.08.2019
comment
Di SELECT, nama kolom Anda adalah MAX(STATUS_DATE). Di ORDER BY Anda, itu STATUS_DATE. Apa yang kamu harapkan? Bagaimana Anda mengharapkan SQL Server mengetahui bahwa Anda STATUS_DATE di ORDER BY merujuk ke MAX(STATUS_DATE) in SELECT`?   -  person Eric    schedule 28.08.2019

Jawaban (2)


arrow_upward
0
arrow_downward

Tapi saya memiliki STATUS_DATE dalam fungsi agregat MAX dalam SELECT.

Hal ini karena agregasi, seperti biasa STATUS_DATE menghilang saat agregasi, dalam kasus Anda, urutannya tidak akan dilakukan kecuali Anda menentukan alias MAX(STATUS_DATE) AS STATUS_DATE

Namun, saya yakin, ROW_NUMBER() dan Sub kueri akan membantu Anda dalam kasus Anda, coba ikuti:

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

Anda dapat melakukan ini dengan beberapa cara:

Menggunakan nomor_baris()

  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

Atau dengan subkueri:

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

Namun pada keduanya, Anda harus berhati-hati dalam menangani ikatan.

person Juan Carlos Oropeza    schedule 28.08.2019
comment
Saya mencoba menerapkan contoh yang Anda tunjukkan kepada saya. Saya akan memperbarui pertanyaan apakah saya membuatnya berhasil, atau mengalami tantangan. Terima kasih banyak! - person erasmo carlos; 28.08.2019