April 2, 2016 at 1:35 pm
I'm using the SELECT below to pull a list of parts and rank them by the number of times that they have been ordered. I would like to take them and then convert the 'Ranking' in high level text categories:
Top 20% Show Actual Ranking
2nd 20% Display Ranking as 'High'
3rd 20% Display Ranking as 'Medium'
4th 20% Display Ranking as 'Low'
5th 20% Display Ranking as 'Very Low'
Thus the results might appear as
Row_Number() Ranking Part
1 1 Jim Bean
2 2 Maker's Mark
3 3 Jack Daniels
4 High iPhone 6
5 High iPhone 6s
6 High iPhone 5
7 Medium Dell Latitude 6430
8 Medium ID Tag
9 Medium Class Ring
10 Low Rock
11 Low Cigar Cutter
12 Low Cigar
13 Very Low Torch
14 Very Low Tape Measure
15 Very Low USB Cable
(And yes, I just described the objects on my desk.)
SELECT
*, ROW_NUMBER() OVER (ORDER BY Total_Orders Desc) Ranking
FROM
(SELECT
dbo.Parts.NameLong Order_Item,
(SELECT COUNT(PartId) AS Expr1 FROM dbo.Orders WHERE (PartId = dbo.Parts.Id)) AS Total_Orders
FROM
dbo.Parts) s1
April 3, 2016 at 1:13 am
It sounds like you might benefit from the NTILE function. Keep your ROW_NUMBER, but you can use your NTILE result to join to a table (either physical or table-valued-constructor) to display your rank. Here's an example, but it's untested.
WITH cteOrders AS (
SELECT p.NameLong, TotalOrders = COUNT(o.ID)
FROM dbo.Parts p
INNER JOIN dbo.Orders o ON o.PartID = p.ID
GROUP BY p.NameLong
),
cteRanking AS (
SELECT NameLong, TotalOrders,
Ranking = ROW_NUMBER() OVER(ORDER BY TotalOrders DESC)
RankGroup = NTILE(5) OVER (ORDER BY TotalOrders DESC)
FROM cteOrders
)
SELECT r.NameLong, r.TotalOrders, r.Ranking, r.RankGroup,
DisplayRank = CASE WHEN r.RankGroup = 1 THEN CONVERT(Varchar(16), r.Ranking
ELSE rd.Description
END
FROM cteRanking r
CROSS APPLY (VALUES(1, 'Awesome'),
(2, 'High'),
(3, 'Medium'),
(4, 'Low'),
(5, 'Very Low')
) rd
ORDER BY r.Ranking;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply