Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

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
0
jazzcatone
Asked:
jazzcatone
3 Solutions
 
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
 
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
 
jazzcatoneAuthor Commented:
Thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now