Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Find correct record using  ;WITH

Posted on 2016-10-25
7
Medium Priority
?
38 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 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 66

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Screencast - Getting to Know the Pipeline

971 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