pigsty | PostgreSQL in Great STYle, Battery-Included Free RDS Alternative! | Database library
kandi X-RAY | pigsty Summary
kandi X-RAY | pigsty Summary
Pigsty (/ˈpɪɡˌstaɪ/) is the abbreviation of "PostgreSQL In Graphic STYle". License: Apache 2.0 License.
Support
Quality
Security
License
Reuse
Top functions reviewed by kandi - BETA
Currently covering the most popular Java, JavaScript and Python libraries. See a Sample of pigsty
pigsty Key Features
pigsty Examples and Code Snippets
Community Discussions
Trending Discussions on pigsty
QUESTION
I’m writing a query against a table of fictional insurance clams called CLAIMS, using RANDOMLY GENERATED FICTIONAL NAMES AND DATA.
There are 5 distinct categories in the column called PRIMARY_DX: Alcoholism, Anxiety Disorder, Depression, Psychosis, Substance Use Disorder
The other main columns are PATIENT_ID and CLAIM_PAID_AMT
I want to sum up the CLAIM_PAID_AMT per PATIENT per PRIMARY_DX and list only the top 5 patients who have the highest sum per PRIMARY_DX
The only way I could think to do this was with two Common Table Expressions, where in CTE1 I partition by PRIMARY_DX and PATIENT_ID and SUM the CLAIM_PAID_AMT for each PATIENT.
Then in CTE2 I use a ROW_NUMBER function on CTE1, to partition by PRIMARY_DX and sort by the TotalClaims DESC and select the top 5 from each PRIMARY_DX.
I’ve been writing SQL for less than 2 years and was wondering if this could be accomplished in one CTE or perhaps with some form of Cross Apply?
I’m including my code and the output below.
...ANSWER
Answered 2018-Oct-17 at 21:53You should only need one sub-query or CTE since you can use the aggregate within the ROW_NUMBER()
.
Here is an approach using the sub-query:
Community Discussions, Code Snippets contain sources that include Stack Exchange Network
Vulnerabilities
No vulnerabilities reported
Install pigsty
Support
Reuse Trending Solutions
Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from over 650 million Knowledge Items
Find more librariesStay Updated
Subscribe to our newsletter for trending solutions and developer bootcamps
Share this Page