Andreas P. Williams (9/20/2013)
Hi, mickyT!Your method only works if the number of rows is a multiple of 5. Try and remove the ('J',1) and your bottom 20 per cent has only one record.
True .. but with more records in the result sets and a higher NTILE value, this could be refined. The other problem with this is that it will pretty much do only percentages rather than number of records.
RANK could also be used for this with a little effort
WITH cte AS (
SELECT Name, Quantity,
RANK() OVER (ORDER BY Quantity DESC) TopRank,
RANK() OVER (ORDER BY Quantity ASC) BottomRank,
COUNT(*) OVER (ORDER BY Quantity) / 2 Midpoint
FROM Books
)
SELECT Name, Quantity, TopRank,
CASE WHEN TopRank <= 100 THEN 'Best Seller'
WHEN BottomRank <= 100 THEN 'Worst Seller'
ELSE 'Average Seller'
END
FROM cte
WHERE TopRank <= 100 or
BottomRank <= 100 or
TopRank between Midpoint - 50 and Midpoint + 50
Unfortunately I can't test and refine this at the moment, so there may be some quirks (errors:w00t:) I can't envisage.