Solved

SQL top 5 within group

Posted on 2013-12-22
3
631 Views
Last Modified: 2013-12-22
Hi there,

I have a grouped, summed query, grouped on PSeason, Name(of client), summed on totalamount, ordered by PSeason, totalamount (desc).

What I need to do is only return the top 5 clients in each season, by highest totalamount. This query currently returns all clients.

I am stumped as to how to do it. I assume I will put this query as a subquery into a another query but beyond that, I am stuck.

Can you help me?

Many thanks in advance,

John
Grouped.txt
d-client.xlsx
d-time.xlsx
f-sales.xlsx
0
Comment
Question by:JohnSaint
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Peter Kwan earned 500 total points
ID: 39734526
You may try the following:

SELECT PSEASON, NAME, SumOfTotalAMOUNT FROM
(select d_time.PSEASON, d_client.NAME, sum(f_sales.TOTALAMOUNT) AS SumOfTotalAMOUNT, RANK() OVER (PARTITION BY PSEASON ORDER BY sum(f_sales.TOTALAMOUNT) DESC) RANK
FROM f_sales, d_time, d_client
WHERE f_sales.TIME_ID = d_time.TIME_ID 
AND f_sales.CLIENT_ID = d_client.CLIENT_ID
GROUP BY  d_time.PSEASON, d_client.NAME
) WHERE RANK <= 5

Open in new window

0
 

Author Comment

by:JohnSaint
ID: 39734538
Great thanks. That works well...
0
 

Author Closing Comment

by:JohnSaint
ID: 39734539
Cheers!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with a script Updating records from other records in the Same table 13 49
Filtering characters in an SQL field 2 16
SQL Syntax 24 43
Procedure syntax 5 38
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

809 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