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!

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 :)

For MySql you can do something like the following:

select OrderNO, PartCode, Quantity from table a
join (select ID, MAX(DateEntered) from table group by OrderNO) b on a.ID = b.ID
 

rownumber() over(...) is working but I didn't like this solution for 2 reasons.

• This function is not available when you using older version of SQL like SQL2000
• Dependency on function and is not really readable.

Another solution is:

SELECT tmpall.[OrderNO] ,
       tmpall.[PartCode] ,
       tmpall.[Quantity] ,
FROM   (SELECT [OrderNO],
               [PartCode],
               [Quantity],
               [DateEntered]
        FROM   you_table) AS tmpall
       INNER JOIN (SELECT [OrderNO],
                          Max([DateEntered]) AS _max_date
                   FROM   your_table
                   GROUP  BY OrderNO ) AS tmplast
               ON tmpall.[OrderNO] = tmplast.[OrderNO]
                  AND tmpall.[DateEntered] = tmplast._max_date

Try to avoid IN use JOIN

SELECT SQL_CALC_FOUND_ROWS *  FROM (SELECT  msisdn, callid, Change_color, play_file_name, date_played FROM insert_log
   WHERE play_file_name NOT IN('Prompt1','Conclusion_Prompt_1','silent')
 ORDER BY callid ASC) t1 JOIN (SELECT MAX(date_played) AS date_played FROM insert_log GROUP BY callid) t2 ON t1.date_played=t2.date_played

This worked for me perfectly fine.

    select name, orderno from (
         select name, orderno, row_number() over(partition by 
           orderno order by created_date desc) as rn from orders
    ) O where rn =1;

This works for me. use MAX(CONVERT(date, ReportDate)) to make sure you have date value

select max( CONVERT(date, ReportDate)) FROM [TraxHistory]