Solved

# SQL Query Question

Posted on 2012-09-20
366 Views
Last Modified: 2012-10-18
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
Question by:jazzcatone
5 Comments

LVL 65

Assisted Solution

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

LVL 7

Assisted Solution

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

LVL 2

Accepted Solution

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

Author Closing Comment

Thanks
0

LVL 65

Expert Comment

Thanks for the split.  Good luck with your project.  -Jim
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

### Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 9 30
Salary Amount Format 13 50
consolidate 4 lines of oracle query output to 1 line 4 23
SQL Agent Timeout 5 26
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### 761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!