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

Question

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!

From your comment, go with the ROW_NUMBER() answer. It may look longer, but it is, in my experience, much the fastest with appropriate indexes. — MatBailie, Aug 19 '11 at 07:37
@GEMI just out of curiosity, Doesn't `MAX(DATE)` returns one line for order 9999? — xameeramir, Jan 25 '16 at 10:35
Yes, but I wanted each different order to return only the last order line. — GEMI, Feb 10 '16 at 07:33
Duplicated by https://stackoverflow.com/questions/16550703/sql-get-the-last-date-time-record and https://stackoverflow.com/questions/18393158/sql-server-selecting-records-with-most-recent-date-time — Vadzim, Nov 02 '17 at 18:07

score 229 · Accepted Answer · edited Jan 07 '14 at 12:19

229

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

edited Jan 07 '14 at 12:19

BenMorel

31,815
47
169
296

answered Aug 19 '11 at 07:22

Mikael Eriksson

132,594
21
199
273

3

Thanks Mikael Eriksson, this is an awesome query! – GEMI Aug 19 '11 at 10:10

score 68 · Answer 2 · edited Sep 03 '14 at 17:42

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)

score 37 · Answer 3 · answered Nov 04 '14 at 03:39

37

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.

answered Nov 04 '14 at 03:39

tone

1,220
15
43

4

This turned out to me marginally faster than the other solutions here on a ~3.5M row dataset, however SSMS suggested an index that cut execution time in half. Thanks! – easuter Aug 20 '15 at 08:38
1

Fast and straightforward. Thanks. – Stephen Zeng Nov 19 '15 at 02:16
1

I have 100k rows and for me Mikael Eriksson's query 3 times faster. Maybe it's because I have ROUND function in partition by clause. – Wachburn Nov 14 '16 at 19:14
3

If you have a date field with the same value (04/15/2017) for 2 differents ID, it will return 2 rows... – Portekoi Apr 15 '17 at 10:28
Yes Portekoi, that is true, but without any other way to differentiate the two rows, how can you select one over the other? You could put a TOP on the result, but how do you know that its not the other row that you want? – tone Apr 30 '17 at 00:28
Wachburn, the result often depends on the indexes applied to the underlying tables. If you don't have the right indexes, the query could end up being a table scan, and hence the wide variance in results. – tone Apr 30 '17 at 00:30

score 14 · Answer 4 · answered Aug 19 '11 at 07:19

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.

score 3 · Answer 5 · answered Oct 07 '14 at 05:01

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

score 3 · Answer 6 · answered Jan 04 '18 at 13:29

3

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

answered Jan 04 '18 at 13:29

MortenB

1,874
19
28

score 1 · Answer 7 · answered Jul 28 '17 at 21:34

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

bencobb · Answer 8 · 2011-08-19T14:55:09.173

1

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

edited Aug 19 '11 at 14:55

answered Aug 19 '11 at 07:23

bencobb

618
4
11

You can't select ID in the inner table if you group by Order No. – Jacob Aug 19 '11 at 07:24
@Dems thanks@cularis yes, this is referring to MySql, the question did not specify what database engine – bencobb Aug 19 '11 at 07:39
1

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Aug 19 '11 at 07:39
This is MSSQL, sorry for that. – GEMI Aug 19 '11 at 08:30

score 0 · Answer 9 · answered May 14 '17 at 11:06

0

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

answered May 14 '17 at 11:06

ANIK ISLAM SHOJIB

2,652
1
25
29

1

Why avoid IN? Do you have any arguments to support your opinion? – Preza8 Mar 08 '18 at 21:41
it will take long time to execute your query. You can read following article https://www.xaprb.com/blog/2006/06/28/why-large-in-clauses-are-problematic/ – ANIK ISLAM SHOJIB Mar 12 '18 at 08:06
@anik This is an article from 2006. Do you have any recent proof of what you are saying? – Félix Adriyel Gagnon-Grenier Apr 09 '18 at 16:33

score 0 · Answer 10 · answered Jul 11 '20 at 08:32

0

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;

answered Jul 11 '20 at 08:32

Shubhankar Sarkar

1
3

1

Beside indentation, this doesn't provide any difference than answer from Mikael Eriksson's answer. – bizi Jan 04 '21 at 23:19

score -2 · Answer 11 · answered Feb 20 '20 at 15:19

-2

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

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

answered Feb 20 '20 at 15:19

user2662006

2,186
20
16

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

11 Answers11

Linked

Related