Solved

getting records with max result date for each customer

Posted on 2013-12-24
3
408 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

791 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