Solved

getting records with max result date for each customer

Posted on 2013-12-24
3
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

739 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