Solved

# finding top 3 rows without using partion or any functioni

Posted on 2009-04-27
197 Views
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
``````
0
Question by:gvamsimba

LVL 41

Expert Comment

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)
``````
0

LVL 44

Accepted Solution

I also am not clear why your manager wants that...
There's the plain old TOP predicate... The corrolated subquery likely not offering blistering performance...

SELECT * FROM
tblBooze T
WHERE ID In
(SELECT TOP 3 ID
FROM tblBooze T1
WHERE T1.COUNTRY  = T.COUNTRY
ORDER BY T1.volume DESC)

Of course - if there are numerous occurances of each brand per country (which would be consistent with your earlier statement using SUM of volume)
Something like:

SELECT  T1.Brand, T1.Country, T1.TotalVol FROM
(SELECT  T.Country, T.Brand, Sum(T.Volume) As TotalVol
FROM tblBooze T
GROUP BY T.Country, T.Brand
) T1
WHERE T1.Brand In
(SELECT TOP 3 T2.Brand
FROM tblBooze T2
WHERE T1.Country  = T2.Country
GROUP BY T2.Country, T2.Brand
ORDER BY Sum(T2.Volume) DESC
)
ORDER BY
T1.Country,  T1.TotalVol DESC
0

Author Closing Comment

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

## Featured Post

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.