asked on

finding top 3 rows without using partion or any functioni

HI , Below is my code which gives me top 3 brands based on volume by country....code is running fine..
but my manager wants to get the same output WITHOUT using any Partion or any other special functions..
is it possible to re-write this query without them ?

Many thanks

select * from (
select brand,country,sum(Volume)a
 
s vol,
row_number() over (partition by country order by sum(volume) desc) r
from Table
group by brand, country
) sq
where sq.r <= 3
order by country

Open in new window

ralmada

I'm not sure why you cannot use the above solution, since it's the most efficient. anyhow. A possibility will be to create a temp table adding an identity field. This solution works well in SQL 2000. See below:

create table #temp_table (
brand varchar(30),
country varchar(30),
totalvolume decimal(10,2)
rowid int identity
)
 
 
insert #temp_table
SELECT brand, country, sum(volume) as totalvolume from 
FROM yourtable1 AS t1
GROUP BY brand, country
order by 1, 2, 3 DESC
 
--And to query the table:
select * from #temp_table a
where a.rowID in (select top 3 rowID from #temp_table where brand = a.brand and country = a.country)

Open in new window

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gvamsimba

ASKER

good..the second part of the solution has given me what i want...cheers..