getting records with max result date for each customer

Posted on 2013-12-24
Medium Priority
Last Modified: 2013-12-25
Please see attached file. I need to extract the most recent results for each customer.
the table contains multiple results per customer. I need data from all columns in the sample attached.

I know it can be accomplished by using "Having" clause, but not sure how.

Please help.

Sample data attached in xls file.

Thank You.
Question by:patd1
  • 2
LVL 16

Accepted Solution

Surendra Nath earned 1000 total points
ID: 39738637
Instead of having clause you can use the row_number along with a CTE to get the required result

SELECT *,row_NUMBER() over(partition by cust_id,result_time order by result_time desc) rn
FROM <Your Table Name>
SELECT * FROM CTE where rn = 1

Open in new window

in the above statement replace <your table name> with your customer table name.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39738734
This is a a minor correction to the above solution (no points please).  You may have better luck using:
Instead of:
ROW_NUMBER() OVER (PARTITION BY cust_id, result_time ORDER BY result_time DESC) rn
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 39738740
If for some reason you cannot use a CTE (for example due to database compatibility level) then you can do it with a derived table as follows:
FROM    YourTableName t
        INNER JOIN (SELECT  cust_id,
                            MAX(Result_Time) LastResult_Time
                    FROM    YourTableName
                    GROUP BY cust_id
                   ) d ON t.Cust_id = d.cust_id
                          AND t.Result_Time = d.LastResult_Time

Open in new window

One caveat, if there are two dates for the same cust_id that are the same then this solution will return both rows.  The CTE solution will only return one.  (There is no guarantee which row it will pick).

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

586 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