Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Sql server sorting issues Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 6:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:18 AM
Points: 878, Visits: 2,711
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.

Post #1496834
Posted Friday, September 20, 2013 4:22 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:39 AM
Points: 818, Visits: 2,485
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) I can't envisage.
Post #1497074
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse