SELECT TOP N With Two Variables

Question

I have the following example in a SQL table

Cust Group Sales
A   1   15
A   1   10
A   1   5
A   2   15      
A   2   10
A   2   5
B   1   15
B   1   10
B   1   5
B   2   15
B   2   10  
B   2   5

What I would like to show is the top 2 products per customer, per group sorted descending by Sales i.e.

Cust Group Sales
A   1   15
A   1   10
A   2   15      
A   2   10
B   1   15
B   1   10
B   2   15
B   2   10

I'm assuming I need to declare two variables, Cust and Group, I'm just not sure how to complete this in one fell swoop.

Apologies for the thick question...no excuse. Thanks for any help.

Answer 1

Hi the following works in MSSQL2000

 SELECT tbl.Cust,
        tbl.Group,
        tbl.Sales

 FROM   MyTable tbl

 WHERE (SELECT COUNT(*) 
        FROM   MyTable tbl2 
        WHERE  tbl2.Sales > tbl.Sales 
        AND    tbl2.Group = tbl.Group 
        AND    tbl2.Cust = tbl.Cust) < 2

 ORDER  BY     tbl.Cust ASC, 
               tbl.Group ASC, 
               tbl.Sales DESC

The inner Select Count(*) query works by counting how many records are above the record it is currently looking at - in this case you want there to b either 0 (1st place) or 1 (2nd place).

Note that because of this, if you have more than 2 values sharing the top spot (e.g 3 A/1's all with sales of 15) you will get more than 2 results back.

However, for your test set it returns the correct results and the use of DISTINCT will help if you'd rather get less instead of more results in this instance. Additionally if your records have a distinct recordid this may help you to decide between them.

Answer 2

Here is one you need running over SQL Server 2000 :

select distinct t1.Cust, t1.GroupID, t1.Sale 
from Test t1 inner join Test t2 on t1.Cust = t2.Cust and t1.GroupID = t2.GroupID
where t1.Sale>t2.Sale
order by Cust, GroupID, Sale desc

its equivalence running over SQL Server 2005+ :

select Cust, GroupID, Sale
from (
    SELECT *, rank() over(partition by Cust, GroupID order by sale desc) as n 
    FROM dbo.Test
     ) dt
where dt.n<=2
order by Cust, GroupID, Sale desc