SQL Query Question

How do I select the TotalPts column with only the latest dateofchange for each driverid. I should get the latest totalpoints for each driver basically. 1 totalpts per driver. You'll notice there are multiple entries for some DriverID's.Table data sample is below. Any help would be most appreciated

Jason

TotalPts   DriverID    DateofChange
13          25422        2012-08-16  
13          25422        2011-09-07  
18          25445        2012-04-17  
29          25486        2011-08-22  
15          25535        2012-08-01  
16          25577        2011-11-21  
18          25642        2012-08-01  
18          25642        2011-08-24  
14          25642        2011-08-09  
20          25658        2011-09-07
16          25658        2011-08-08  
18          25681        2012-08-06  
14         25706        2012-04-02  
14         25722        2011-09-07  
14         25729        2012-01-04  
18         25729        2011-09-07  
14         25729        2011-08-11  
14         25738        2011-08-09  
22         25745        2012-08-13  
23         25745        2011-08-16  
13         25854        2012-08-16  
13         25854        2011-09-07  
42         25864        2012-08-14
jazzcatoneAsked:
Who is Participating?
 
BrainfebCommented:
Select * from (
select *,ROW_NUMBER() over(partition by DriverID order by DateofChange desc) As Rownum from <Your Table name>
) A  where A.Rownum=1
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl (change the obvious stuff)...

SELECT yt.TotalPts, yt.DriverID, yt.DateofChange
FROM YourTable yt
JOIN (
    SELECT DriverID, Max(DateofChange) as max_date
    FROM YourTable) yt_max ON yt.DriverID = yt_max.DriverID AND yt.DateOfChange = yt_max.max_date
ORDER BY whatever
0
 
BenefordCommented:
Essentially the same solution as jimhorn, but using a sub-SELECT rather than a JOIN.

SELECT T.TotalPts, T.DriverID, T.DateOfChange
FROM Table1 T
WHERE T.DateOfChange = (Select max(TT.DateOfChange) from Table1 TT Where TT.DriverID=T.DriverID)
0
 
jazzcatoneAuthor Commented:
Thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.