Create Top 3 Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create Top 3 Values

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-09-06 : 12:01:52

Hi

I am trying to create a list of top 3 order values by country/region/sub-region within a table, and I need to extract and combine all top 3 values from another table. So I want to have this:


COUNTRY      REGION      SUB-REGION    VALUE
=======      ======      ==========    =====
EU           SE          EE            250k
EU           SE          EE            200k
EU           SE          EE            100k

But I have about 20 combinations of countrys/regions/sub-regions and need to extract all the values and keep them in order of highest value. You can see the example below with bold highest values for each combination:


COUNTRY      REGION      SUB-REGION    VALUE
=======      ======      ==========    =====
EU           SE          EE            250k
EU           SE          EE            200k
EU           SE          EE            100k
EU           NE          EE            450k
EU           NE          EE            300k
EU           NE          EE            150k
EU           NW          EE            350k
EU           NW          EE            250k
EU           Nw          EE            150k
.....
.....
.....

A query I use for all the regions but just change the values for the combinations is:

SELECT TOP 3 *
FROM RegionOrder r
WHERE r.Country='EU' and r.Region ='SE' and r.Sub_Region = 'EE'
ORDER BY Value DESC

So I can easily select all the values but looking for an easier way than creating a view per combination and then combining them into another view? If I use union in a view create statement I need to create about 90 select statements and because I can only use order by at the end of create view the values do not show as I would like them to show - I would like them to show as per in the table.

Thanks

G

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 12:07:41

select country,region,sub_region,ordervalue
from
(select country,region,sub_region,ordervalue,row_number() over (partition by country,region,sub_region order by ordervalue desc) as rn from RegionOrder)dt
where rn <= 3
order by country asc,region asc,sub_region asc,rn

Too old to Rock'n'Roll too young to die.