选择每个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.

我有以下查询返回数据，如下所示。但我需要排除MODIFIEDDATETIME显示为红色的行，因为它们的COMMITRECID时间戳较低。如数据所示，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

以下是上述查询的结果

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

所需的结果只有8行，绿色为5，黑色为3（红色中的6个应该被消除）。

Thank you very much for your help :)

非常感谢您的帮助：）

2 个解决方案

#1

Use RANK:

使用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.

顺便说一句，请尝试在查询中使用tabla别名而不是整个表名。

*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

*免责声明：您说您想要最大日期，但您帖子中的选定值是具有最小日期的值，因此我在答案中使用了该标准

#2

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

此方法仅将您的历史记录表限制为具有您所描述的MINdate的历史记录表。

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

#1