Can anyone help me to group student scores into quintile? I think there is a feature in SQL Server 2012, but still we havent upgraded to it as we are using 2008R2. I tried
Ntile(5)` but it is not generating the desired result. I need below Quintile column
Student Score Quintile
------------------------
Student1 20 1
Student2 20 1
Student3 30 2
Student4 30 2
Student5 40 2
Student6 40 2
Student7 50 3
Student8 50 3
Student9 60 3
Student10 70 4
Student11 70 4
Student12 80 4
Student13 80 4
Student14 90 5
Below is the correct answer given by Erland Sommarskog
Create Table #Scores(Student varchar(20), Score int);
Insert #Scores(Student, Score) Values
('Student1', 20)
,('Student2', 20)
,('Student3', 30)
,('Student4', 30)
,('Student4', 30)
,('Student4', 30)
,('Student5', 40)
,('Student6', 40)
,('Student7', 50)
,('Student8', 50)
,('Student9', 60)
,('Student10', 70)
,('Student11', 70)
,('Student12', 80)
,('Student13', 80)
,('Student14', 90);
; WITH quintiles AS (
SELECT Score, ntile(5) OVER(ORDER BY Score) AS quintile
FROM (SELECT DISTINCT Score FROM #Scores) AS s
)
SELECT s.Student, s.Score, q.quintile
FROM #Scores s
JOIN quintiles q ON s.Score = q.Score
go
DROP TABLE #Scores
--by Erland Sommarskog``