• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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

0
gvamsimba
Asked:
gvamsimba
1 Solution
 
ralmadaCommented:
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

0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
gvamsimbaAuthor Commented:
good..the second part of the solution has given me what i want...cheers..
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now