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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-01-23 : 07:16:18
|
| Eric writes "I have a table with saleprice ($), productID (int), & companyID (int). I have many companies purchasing products and many products. The distinct list of products and companies grows and shrinks constantly. I want, in one result set, the top 5 companies' sales per product. I know that I could do this using a cursor, but I would like to be able to do this in a single query. My problem is TOP 5 only returns 5 rows and my result set needs to be at most 5 * number of products I have had sales for (could be less if there was less than 5 sales for a particular product)." |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-23 : 08:45:52
|
| select distinct saleprice, productID, companyIDfrom t whereproductID <= any(select distinct top 5 productID from t tt where tt.saleprice=t.saleprice order by 1)andcompanyID <= any(select distinct top 5 companyID from t tt where tt.saleprice=t.saleprice order by 1) |
 |
|
|
|
|
|
TOP X per ResultSet