Monday, March 19, 2012

How to use a column name as an argument in a TOP clause

I needed to use a column name as an argument in a TOP clause recently in SQL Server.  I had to research and experiment quite a bit to find what I was looking for, so I put together some sample code that should help get you started, if you're trying to do the same thing.

Note that in the last result set, with the Top clause, the number of records from each Market corresponds to the associated value in the MarketType.TopCustomersToGet. I'm using a cross apply here, which worked for me. Be sure and test performance, some sources indicate that you might get better performance with a CTE.

Declare @MarketType table (MarketTypeID tinyint, MarketImportance varchar(10), TopCustomersToGet int)
insert into @MarketType  values
(1  , 'High', 4)
,(2 , 'Low' , 2)

Declare @Market Table (MarketName varchar(20), MarketTypeID int) 
insert into @Market values
('Paris'    ,1)
,('London'  ,1)
,('Miami'   ,2)
,('Seattle' ,2)

Declare @Customer table (MarketName varchar(20), CustomerName varchar(20), CustomerRank int)
insert into @Customer  values
('Paris','Wayne',1)
,('Paris','Colleen',2)
,('Paris','Manuel',3)
,('Paris','Michelle',4)
,('Paris','Jesse',5)
,('London','Jenny',1)
,('London','Patrick',2)
,('London','Megan',3)
,('London','Alice',4)
,('London','Olga',5)
,('Miami','Brandon',1)
,('Miami','Alfonso',2)
,('Miami','Benjamin',3)
,('Miami','Harry',4)
,('Miami','Stephen',5)
,('Seattle','Willie',1)
,('Seattle','Allen',2)
,('Seattle','Megan',3)
,('Seattle','Danny',4)
,('Seattle','Manuel',5)

Select * from @MarketType
Select * from @Market
Select * from @Customer

Select 
    MarketType.MarketImportance
    ,Market.MarketName
    ,TopCustomers.CustomerName
From @Market Market
    join @MarketType MarketType
        on Market.MarketTypeID = MarketType.MarketTypeID 
Cross apply (
    Select top 
        (MarketType.TopCustomersToGet)
        CustomerName
    from @Customer Customer
    where 
        Customer.MarketName = Market.MarketName
    order by
        Customer.CustomerRank desc
    ) as TopCustomers

No comments:

Post a Comment