CODEWITHSUNDEEP
sqlsql-server
vmrao
vmrao

Reputation: 87

Select all rows with max date for each ID

I have the following query returning the data as shown below. But I need to exclude the rows with MODIFIEDDATETIME shown in red as they have a lower time stamp by COMMITRECID. As depicted in the data, there may be multiple rows with the max time stamp by COMMITRECID.

SELECT REQCOMMIT.COMMITSTATUS, NOTEHISTORY.NOTE, NOTEHISTORY.MODIFIEDDATETIME,   NOTEHISTORY.COMMITRECID
FROM REQCOMMIT INNER JOIN NOTEHISTORY ON REQCOMMIT.RECID = NOTEHISTORY.COMMITRECID 
WHERE REQCOMMIT.PORECID = 1234

Here is the result of the above query

Query Result

The desired result is only 8 rows with 5 in Green and 3 in Black (6 in Red should get eliminated).

Thank you very much for your help :)

Upvotes: 1

Views: 260

Answers (2)

S3S
S3S

Reputation: 25112

This method just limits your history table to those with the MINdate as you described. 

SELECT 
    REQCOMMIT.COMMITSTATUS, 
    NOTEHISTORY.NOTE, 
    NOTEHISTORY.MODIFIEDDATETIME,   
    NOTEHISTORY.COMMITRECID
FROM REQCOMMIT 
INNER JOIN NOTEHISTORY ON REQCOMMIT.RECID = NOTEHISTORY.COMMITRECID 
INNER JOIN (SELECT COMMITRECID, MIN(MODIFIEDDATETIME) DT FROM NOTEHISTORY GROUP BY COMMITRECID) a on a.COMMITRECID = NOTEHISTORY.COMMITRECID  and a.DT = NOTEHISTORY.MODIFIEDDATETIME
WHERE REQCOMMIT.PORECID = 1234

Upvotes: 1

Lamak
Lamak

Reputation: 70638

Use RANK:

WITH CTE AS
(
    SELECT  R.COMMITSTATUS, 
            N.NOTE, 
            N.MODIFIEDDATETIME,   
            N.COMMITRECID,
            RN = RANK() OVER(PARTITION BY N.COMMITRECID ORDER BY N.MODIFIEDDATETIME)
    FROM REQCOMMIT R
    INNER JOIN NOTEHISTORY N
        ON R.RECID = N.COMMITRECID 
    WHERE R.PORECID = 1234 
) 
SELECT *
FROM CTE
WHERE RN = 1;

As an aside, please try to use tabla aliases instead of the whole table name in your queries.

*Disclaimer: You said that you wanted the max date, but the selected values in your post were those with the min date, so I used that criteria in my answer

Upvotes: 2

Related Questions