SQL Server: SELECT only rows with MAX (DATE)

Question

I have a data table (db - 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 request that returns OrderNO, PartCode and Quantity, but only for the last registered order.

In the example table, I would like to receive the following information:

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

Please note that for order 9999, only one row was returned.

Thank!

Answer 1

If rownumber() over(...) is available to 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 

Answer 2

The best way is Michael Ericsson if ROW_NUMBER() is available to you.

The following is best - join the query as Cularis answer.

Alternatively, the simplest and most direct way is to correlate the 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) 

Answer 3

 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 requests listed above. The cost of the request was 0.0070668.

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

You may not care about performance for such a small sample, but in large queries all this is added.

Answer 4

 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 internal query selects all OrderNo with the maximum date. To get other table columns, you can join them on OrderNo and MaxDate .

Answer 5

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 

Answer 6

And you can also use this select statement as a left connection request ... 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 helps someone looking for this :)

Answer 7

Try to avoid using IN 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 

Answer 8

rownumber () over (...) works, but I did not like this solution for two reasons. - This function is not available if you are using an older version of SQL, such as SQL2000 - The dependency on the function is not actually readable.

Another solution:

 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