Solved

SQL top 5 within group

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

Suggested Solutions

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now