Solved

SQL top 5 within group

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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