0

Here is the criteria I am looking for (1) rank data by region (2) include top 3 for two regions and top 5 for other region

I am able to get criteria (1) using the query below:

select rank() over (partition by region order by sales_score desc) rank_by_region,
       region,
       sales_person
       sales_score
from sales_owner.data

If I get three regions (North, South, West). How would I order the rank that the output would include top 3 for North and South and top 5 for West?

Any help greatly appreciated!

Ariel
  • 928
  • 4
  • 15
  • 32

1 Answers1

3

Use a where condition with or.

select region,sales_person,sales_score from
(
select rank() over (partition by region order by sales_score desc) rank_by_region,
       region,
       sales_person
       sales_score
from sales_owner.data
) t
where (region in ('North','South') and rank_by_region <= 3)
or (region = 'West' and rank_by_region <= 5)
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • This solution is 100% correct. It may be a little more efficient if written as `where rank_by_region <= 3 or (region = 'West' and rank_by_region <= 5)` - although I'd say vkp's solution is more readable, which is an important benefit. –  May 24 '16 at 16:28