SQL Query Question

Posted on 2012-09-20
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


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
Question by:jazzcatone
    LVL 65

    Assisted Solution

    by:Jim Horn
    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
    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)
    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

    Author Closing Comment

    LVL 65

    Expert Comment

    by:Jim Horn
    Thanks for the split.  Good luck with your project.  -Jim

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    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.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now