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? — Zameer Ansari, 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 242 · Accepted Answer · edited Jan 07 '14 at 12:19

242

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

34,448
50
182
322

answered Aug 19 '11 at 07:22

Mikael Eriksson

136,425
22
210
281

score 74 · 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 42 · Answer 3 · answered Nov 04 '14 at 03:39

42

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,374
20
47

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
Just used this method to update one of our queries at work. This method returned the same results as our INNER JOIN version but reduced the number of reads from 5,460,413 down to 92,483. Unbelievable improvement! Thank you!!! – Derek K Nov 04 '22 at 18:53

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 4 · Answer 5 · answered Jan 04 '18 at 13:29

4

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

2,749
1
31
35

score 3 · Answer 6 · 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 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

score 1 · Answer 8 · answered Jul 11 '20 at 08:32

1

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

11
4

2

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

bencobb · Answer 9 · 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 10 · 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

3,002
1
27
36

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
1

@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 11 · answered Oct 10 '22 at 18:46

To add to Mikael Eriksson's answer, there's a nuance in how you should use this, when you have duplicate (OrderNO, PartCode) rows with the same date. You can use:

row_number() will return only one row, removing duplicates
rank() will return all duplicate rows

It turns out I needed rank, not row_number

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

score -2 · Answer 12 · 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,246
22
16

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

12 Answers12

Linked

Related