Solved

getting records with max result date for each customer

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

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 70
T-SQL Using IN with a subquery 3 12
BULK INSERT most recent CSV 19 17
Recommendation vb6 to vb.net or others 14 25
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

12 Experts available now in Live!

Get 1:1 Help Now