SQL How to return the record with the most recent date and also highest int value

Question

I have to return the most recent row in a SQL database, grouped by CallID. The 'most recent' in this instance means the most recent DepartureDate and the highest Departuretime (int) on that date. See below example for your reference:

set dateformat dmy
create table #Temp
(
Callid nvarchar(6), 
Linenum nVarchar(6), 
Engineerid nvarchar (4), 
Departuredate DateTime, 
Departuretime int
)
insert into #Temp 
Values (
'100000','1','AToo','05/09/2017','57896'),
('100000','1.5','DBok','05/09/2017','57898'),
('100000','1.75','DBok','05/09/2017','57895'),
('100000','2','GKar','04/09/2017','59805'),
('100000','3','ALee','05/09/2017','54895'),
('100001','1','GKar','08/09/2017','54000'),
('100001','2','GKar','08/09/2017','58895'),
('100001','2.25','ALee','08/09/2017','56875'),
('100001','2.5','DBok','07/09/2017','59000')

select * from #Temp
drop table #Temp

What I want to return for the above, is the below two records:

CallID  Linenum  Engineerid  Departuredate  Departuretime
100000  1.5      DBok        05/09/2017     57898
100001  2        GKar        08/09/2017     58895

This has been asked and answered many many times but you did a GREAT job posting the details. If you provide such good detail and consumable data in your posts you will continue to get great answers. Now if you could just teach everybody else to post nice clear questions. :D — Sean Lange
– Sean Lange, Commented Sep 10, 2018 at 13:54
Sorry Sean, went searching in Google and found references to the Row_Number per Mureinik, but I didnt see the key bit which was adding Desc (or Asc) after each order by column in the OVER clause. That did the trick! Will remember this for next time! — Naz
– Naz, Commented Sep 10, 2018 at 14:40
Nothing to apologize for at all. You did a great job posting your question. I look forward to the next one. — Sean Lange
– Sean Lange, Commented Sep 10, 2018 at 14:57

Mureinik · Accepted Answer · 2018-09-10 13:52:56Z

5

You can use the row_number window function to order the records per callid and then just take the first:

SELECT CallID, Linenum, Engineerid, Departuredate, Departuretime
FROM   (SELECT CallID, Linenum, Engineerid, Departuredate, Departuretime,
               ROW_NUMBER() OVER (PARTITION BY CallID
                                  ORDER BY Departuredate DESC, Departuretime DESC) AS rn
        FROM   #Temp) t
WHERE  rn = 1

answered Sep 10, 2018 at 13:52

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Naz Over a year ago

Brill! Forgot about the Desc after the order by, that did the trick!

Gottfried Lesigang · Accepted Answer · 2018-09-10 13:54:38Z

4

You can use a trick with TOP 1 WITH TIES

select TOP 1 WITH TIES * 
from #Temp
ORDER BY ROW_NUMBER() OVER (PARTITION BY CallId ORDER BY Departuredate DESC,Departuretime DESC);

ORDER BY ROW_NUMBER() OVER(PARTITION BY CallId...) will restart the row numbering for each CallId. So there are many rows with a 1 marking the highest. TOP 1 would pick just the first row of the result set, but with WITH TIES it will return all first rows, if there are more than one.

answered Sep 10, 2018 at 13:54

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

SQL How to return the record with the most recent date and also highest int value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related