Ranking problems

[torias]

Hi all,

I'm trying to ultimately create a report which displays the top 3 best-selling products. I've used the TOP function in the query to only show the top three results, however if I have two products with the same sales amount, it only essentially shows the top 'two'.

e.g.

PRODUCT SALES
Pink T-Shirt 10
Blue T-Shirt 8
Red T-Shirt 8

So, if there is a tie, I want it to show the next product down (the fourth product) as well.

PRODUCT SALES
Pink T-Shirt 10
Blue T-Shirt 8
Red T-Shirt 8
Orange T-Shirt 7

Any ideas?

 

[Banana]

Off the cuffs,

I'd use a subquery that did a SELECT DISTINCT TOP 3 MAX() for the sales, then in the main query, use that subquery as a criteria, so it can return a variable number of records where they have same amount of sales in top 3 returned in subquery.

Hope that gives you an idea...

 

[georgedwilkinson]

Try:

select allthestuffyouneedselected 
from whereveryouregettingitfrom 
where qty in (select top 3 qty from whereveryouregettingitfrom group by qty order by qty);

Interestingly enough, even though you're only getting the top 3 volumes sold, this query will return 3 or more rows (potentially millions).