Solved

getting records with max result date for each customer

Posted on 2013-12-24
3
403 Views
Last Modified: 2013-12-25
Cust-Results.xls
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.
0
Comment
Question by:patd1
  • 2
3 Comments
 
LVL 16

Accepted Solution

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

;WITH CTE AS
(
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.
0
 
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:
ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY result_time DESC) rn
Instead of:
ROW_NUMBER() OVER (PARTITION BY cust_id, result_time ORDER BY result_time DESC) rn
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 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:
SELECT  t.*
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).
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

785 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