Search code examples
sql-serverselectmax

SQL Server: SELECT only the rows with MAX(DATE)

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

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

Notice that only one line was returned for order 9999.

Thanks!

Solution

  • 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