Loop for SELECT TOP n... - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Loop for SELECT TOP n...

Author

Topic

luzippu
Starting Member

23 Posts

Posted - 2013-10-07 : 09:29:08

Hi,

i have something similar to this:

SELECT TOP 10 OrderType, Customer
FROM MyTable
WHERE OrderType = 'Apples'
ORDER BY OrderValue Desc

UNION

SELECT TOP 10 OrderType, Customer
FROM MyTable
WHERE OrderType = 'Oranges'
ORDER BY OrderValue Desc

UNION

SELECT TOP 10 OrderType, Customer
FROM MyTable
WHERE OrderType = 'Bananas'
ORDER BY OrderValue Desc

UNION

etc...

How would you replace the unions with a single loop looking at OrderType as a variable?

The values for OrderType criteria could be in a string if possible or in a separate table. We are talking about 10 to 20 different variables that the code will need to go through.

Note that i've never used while loop, for each etc. in tsql before...

i'm on sql server 2008.

make sense?
thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 10:10:13

You could do the following:

WITH    cte
          AS ( SELECT   ordertype ,
                        customer ,
                        ROW_NUMBER() OVER ( PARTITION BY ordertype, customer ORDER BY ordervalue DESC ) AS RN
             )
    SELECT  ordertype ,
            customer
    FROM    cte
    WHERE   RN <= 10;

If you want with ties, replace the row_number function with RANK. Another way would be to use a cross apply, but this is probably more efficent.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-08 : 07:35:47

Also refer this post http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources