Return rows with youngest DateTime

Question

Return rows with youngest DateTime

jagnew

Mr or Mrs. 500

Points: 504
More actions
October 29, 2012 at 7:16 am

#263803

I have a dataset for a ticketing system. The dataset contains the Ticket #, Assigned Technician, and the DateTime they were scheduled on that ticket.
Given this information, for each Ticket # I need to return the assigned tech who was last scheduled. Eg Below
Dataset
Ticket | Tech | Date
10000 | Tech 1 | 2012-09-20 01:00:00:000
10000 | Tech 2 | 2012-09-25 01:00:00:000
10200 | Tech 1 | 2012-09-10 12:00:00:000
10200 | Tech 2 | 2012-09-15 12:00:00:000
Given the above dataset the required result is
Ticket | Tech | Date
10000 | Tech 2 | 2012-09-25 01:00:00:000
10200 | Tech 2 | 2012-09-15 12:00:00:000

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

jagnew (10/29/2012)
I have a dataset for a ticketing system. The dataset contains the Ticket #, Assigned Technician, and the DateTime they were scheduled on that ticket.
Given this information, for each Ticket # I need to return the assigned tech who was last scheduled. Eg Below
Dataset
Ticket | Tech | Date
10000 | Tech 1 | 2012-09-20 01:00:00:000
10000 | Tech 2 | 2012-09-25 01:00:00:000
10200 | Tech 1 | 2012-09-10 12:00:00:000
10200 | Tech 2 | 2012-09-15 12:00:00:000
Given the above dataset the required result is
Ticket | Tech | Date
10000 | Tech 2 | 2012-09-25 01:00:00:000
10200 | Tech 2 | 2012-09-15 12:00:00:000

Add this to your SELECT:

rn = ROW_NUMBER() OVER(PARTITION BY Ticket ORDER BY Date DESC)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jagnew Mr or Mrs. 500 Points: 504 More actions · Answer 2

Thanks. This is perfect!

Thanks also for the posting guidelines. I hadn't realized how everyone was able to get nicely ordered code into here on their posts