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

Question

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      

The best way is Mikael Eriksson, if ROW_NUMBER() is available to you.

The next best is to join on a query, as per Cularis' answer.

Alternatively, the most simple and straight forward way is a correlated-sub-query in the WHERE clause.

SELECT
  *
FROM
  yourTable AS [data]
WHERE
  DateEntered = (SELECT MAX(DateEntered) FROM yourTable WHERE orderNo = [data].orderNo)

Or...

WHERE
  ID = (SELECT TOP 1 ID FROM yourTable WHERE orderNo = [data].orderNo ORDER BY DateEntered DESC)

select OrderNo,PartCode,Quantity
from dbo.Test t1
WHERE EXISTS(SELECT 1
         FROM dbo.Test t2
         WHERE t2.OrderNo = t1.OrderNo
           AND t2.PartCode = t1.PartCode
         GROUP BY t2.OrderNo,
                  t2.PartCode
         HAVING t1.DateEntered = MAX(t2.DateEntered))

This is the fastest of all the queries supplied above. The query cost came in at 0.0070668.

The preferred answer above, by Mikael Eriksson, has a query cost of 0.0146625

You may not care about the performance for such a small sample, but in large queries, it all adds up.

SELECT t1.OrderNo, t1.PartCode, t1.Quantity
FROM table AS t1
INNER JOIN (SELECT OrderNo, MAX(DateEntered) AS MaxDate
            FROM table
            GROUP BY OrderNo) AS t2
ON (t1.OrderNo = t2.OrderNo AND t1.DateEntered = t2.MaxDate)

The inner query selects all OrderNo with their maximum date. To get the other columns of the table, you can join them on OrderNo and the MaxDate.

If you have indexed ID and OrderNo You can use IN: (I hate trading simplicity for obscurity, just to save some cycles):

select * from myTab where ID in(select max(ID) from myTab group by OrderNo);

And u can also use that select statement as left join query... Example :

... left join (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 ) RESULT on ....

Hope this help someone that search for this :)