Solved

SQL top 5 within group

Posted on 2013-12-22
3
630 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

777 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