Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Return rows with youngest DateTime
Add to briefcase

Return rows with youngest DateTime Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
jagnew
Posted Monday, October 29, 2012 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:29 AM
Points: 10, Visits: 23
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
Post #1378236
ChrisM@Work
Posted Monday, October 29, 2012 7:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1378240
jagnew
Posted Monday, October 29, 2012 9:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:29 AM
Points: 10, Visits: 23
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
Post #1378296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.