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 best 2 products per customer, for each group sorted in descending order by sales ie

 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 assume that I need to declare two variables, Cust and Group, I'm just not sure how to do this in one fell swoop.

Apologies for the fat 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 internal Select Count(*) query works by counting how many records are over the record that he is currently looking at - in this case, you want to have either 0 (1st place) or 1 (2nd place) a place).

Please note that because of this, if you have more than two values ​​sharing the first place (for example, 3 A / 1 all with a sales volume of 15), you will get more than two results.

However, for your test suite, it returns the correct results, and using DISTINCT will help if you prefer to get fewer rather than more results in this instance. Also, if there is a separate entry in your entries, this can help you decide between them.

Answer 2

Here you need to start 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 performed on 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