How can I group student scores into quintile using SQL Server 2008

Question

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 triedNtile(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

There is a NTILE ranking function that ranks data the way you need it.-->http://msdn.microsoft.com/en-us/library/ms175126.aspx You can try Quartile=NTILE(4) OVER(ORDER BY Student DESC, Score DESC) — Ross Bush, Jul 24 '14 at 03:27
Ntile is going to divide it up as evenly as possible, I don't think you'll be able to end up with it the way you are showing it. Here's a simple [SQL Fiddle](http://sqlfiddle.com/#!3/3ddf3/2) demonstrating. — Andrew, Jul 24 '14 at 03:32
Hi Andrew, Thanks for your response. I tried your SQL query, there is one issue. Student 5 and Student 6 have scored 40 each. But with this query, Student 5 is getting rank 3 and Student 6 is getting rank 4. Thats not fair enough. Can you please relook again. — Mike, Jul 24 '14 at 03:54

score 5 · Answer 1 · answered Jul 24 '14 at 04:51

5

You must have been doing something wrong when using NTILE(5) - that IS the function to use!

Here's my test setup:

DECLARE @Students TABLE (StudentID INT IDENTITY(1,1), StudentName VARCHAR(20), Score INT)

INSERT INTO @Students(StudentName, Score)
VALUES ('Student 1', 20), ('Student 2', 20), 
('Student 3', 30), ('Student 4', 30), 
('Student 5', 40), ('Student 6', 40), 
('Student 7', 50), ('Student 8', 50), 
('Student 9', 60), 
('Student 10', 70), ('Student 11', 70), 
('Student 12', 80), ('Student 13', 80), 
('Student 14', 90)


SELECT 
    StudentName, Score, 
    Quintile = NTILE(5) OVER(ORDER BY Score)
FROM    
    @Students

And the output is:

enter image description here

answered Jul 24 '14 at 04:51

marc_s

732,580
175
1,330
1,459

Hi Marc Thanks for your reply. But with the above logic RANK 1 is assigned to Student 2 and Student 3 , however marks is different. Also Student 3 and Student 4 have scored same but Student 2 have got Rank1 and Student 4 have got Rank2. This would be unfair to the students part once report is live. Can you suggest something else. The data would be 100 to 400 records. I need a dynamic SQL which can handle multiple records and any values. – Mike Jul 24 '14 at 08:49
@Mike: I guess you're just expecting something else than what `NTILE` does..... `NTILE` will try to evenly distribute the rows in question into as many groups as you define. It has **no functionality** to see that rows with the same value for `score` all end up in the same group. If you need that, you'll need to write your own logic (whether in T-SQL or a frontend language....) – marc_s Jul 24 '14 at 09:29

score 1 · Answer 2 · answered Jul 24 '14 at 09:37

Borrowed from marc_s +1

DECLARE @Students TABLE (StudentID INT IDENTITY(1,1), StudentName VARCHAR(20), Score INT)

INSERT INTO @Students(StudentName, Score)
VALUES ('Student 1', 20), ('Student 2', 20), 
('Student 3', 30), ('Student 4', 30), 
('Student 5', 40), ('Student 6', 40), 
('Student 7', 50), ('Student 8', 50), 
('Student 9', 60), ('Student 10', 70), 
('Student 11', 70),('Student 12', 80), 
('Student 13', 80),('Student 14', 90)

SELECT s.StudentName, s.Score, qm.maxQ
  FROM @Students as s
  join ( select score, MAX(Quintile) as maxQ
           from ( SELECT Score, Quintile = NTILE(5) OVER(ORDER BY Score)
                    FROM  @Students ) q 
          group by q.score ) qm
    on qm.Score = s.Score

Hi Blam, It worked perfectly for small set of data. However, i tried larger data set with 100 records. It failed. It is not at all showing Rank 3. Below is the 99 records. Student Name Score Student 1 to 5 -> 4 Student 7 to 26 -> 5 Student 27 to 71 -> 6 Student 72 to 98 -> 7 Student 99 -> 9 — Mike, Jul 25 '14 at 04:41
Maybe there is not a 3 you have most of you data at one level - 6. — paparazzo, Jul 25 '14 at 13:41

score 0 · Accepted Answer · answered Aug 13 '14 at 10:50

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``

How can I group student scores into quintile using SQL Server 2008

3 Answers3

Linked