finding top 3 rows without using partion or any functioni

Posted on 2009-04-27
Last Modified: 2012-05-06
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

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 =

    Open in new window

    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...

    tblBooze T
      (SELECT TOP 3 ID
       FROM tblBooze T1
       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
     T1.Country,  T1.TotalVol DESC

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction In my previous article ( 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.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now