Solved

SQL Find correct record using  ;WITH

Posted on 2016-10-25
7
27 Views
Last Modified: 2016-10-25
Using SQL  ;with , I need to only find the latest payment record with the highest payment amount for a customer.
I marked with a *, the records I need to be selected.

I will then take the With data and update another table.

Filea

Cust          Date                AMT
12345       2016/10/03   263,000
12345       2016/10/06   147,000
12345       2016/09/23    563,000  
12345       2016/10/06   153,000  *
12345       2016/10/06     99,000
23456       2016/08/02     15,000
23456       2016/09/13     11,000  *
456789     2016/10/10      66,300 *
564789     2016/03/12      65,000
564789     2016/03/12      65,500 *
etc...
0
Comment
Question by:thayduck
  • 4
  • 3
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41858775
Explain why the ;With is part of the requirement.

Give this a whirl..
;with cte as ( 
   SELECT Cust, Date, AMT, 
      ROW_NUMER() OVER (PARTITION BY Cust ORDER BY Date DESC, AMT DESC) as rank_order
   FROM YourTable ) 
SELECT * FROM cte WHERE rank_order= 1 ORDER BY Cust

Open in new window


< edited after original post to reflect correct answer >
0
 

Author Comment

by:thayduck
ID: 41859038
Your code executes but does not get the correct data for every customer.
For customer 144840, you got 2016/10/06 13,410 but it should have been 2016/10/06  251,578.

I will take that CTE file and update something with it.
With.PNG
WITH1.PNG
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41859158
>For customer 144840, you got 2016/10/06 13,410 but it should have been 2016/10/06  251,578.
You're going to have to speak to the sample data in the original question.  Keep in mind that experts here cannot connect to your data source(s) and run queries, so we are completely dependant on what you post and can only make educated guesses based on that.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:thayduck
ID: 41859183
Sorry, that was just a example so you knew that it was not working right.
The samples I sent you all came back correct.

I made a small change (to your code)  below and that seemed to fix it.

changed lastamt  to  lastamt DESC

Now they all look right as far as I can see.

Thanks for your quick response...
0
 

Author Comment

by:thayduck
ID: 41859186
Your code was actually  AMT (that is what I called it in my example) , I changed to AMT DESC.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41859189
>changed lastamt  to  lastamt DESC
Doh.  My bad, you are correct.  Max amount woud be DESC.
0
 

Author Closing Comment

by:thayduck
ID: 41859203
Thanks again for the very quick response.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

829 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