SQL Server:仅选择具有 MAX(DATE) 的行 [英] SQL Server: SELECT only the rows with MAX(DATE)

问题描述

我有一个数据表(数据库是 MSSQL):

I have a table of data (the db is MSSQL):

ID  OrderNO  PartCode  Quantity DateEntered
417 2144     44917     100      18-08-11
418 7235     11762     5        18-08-11
419 9999     60657     100      18-08-11
420 9999     60657     90       19-08-11

我想查询返回 OrderNO、PartCode 和 Quantity，但仅针对最后注册的订单.

I would like to make a query that returns OrderNO, PartCode and Quantity, but only for the last registered order.

我想从示例表中获取以下信息:

From the example table I would like to get back the following info:

 OrderNO  PartCode  Quantity     
 2144     44917     100      
 7235     11762     5        
 9999     60657     90  

请注意，订单 9999 只返回了一行.

Notice that only one line was returned for order 9999.

谢谢！

推荐答案

如果 rownumber() over(...) 对你可用 ....

If rownumber() over(...) is available for you ....

select OrderNO,
       PartCode,
       Quantity
from (select OrderNO,
             PartCode,
             Quantity,
             row_number() over(partition by OrderNO order by DateEntered desc) as rn
      from YourTable) as T
where rn = 1      

这篇关于SQL Server:仅选择具有 MAX(DATE) 的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！