Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL top 5 within group

Posted on 2013-12-22
3
Medium Priority
?
655 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Peter Kwan earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

618 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