Microsoft SQL Server OVER Clause Dividing Data into equally-partitioned buckets using NTILE
Example
Let's say that you have exam scores for several exams and you want to divide them into quartiles per exam.
-- Setup data:
declare @values table(Id int identity(1,1) primary key, [Value] float, ExamId int)
insert into @values ([Value], ExamId) values
(65, 1), (40, 1), (99, 1), (100, 1), (90, 1), -- Exam 1 Scores
(91, 2), (88, 2), (83, 2), (91, 2), (78, 2), (67, 2), (77, 2) -- Exam 2 Scores
-- Separate into four buckets per exam:
select ExamId,
ntile(4) over (partition by ExamId order by [Value] desc) as Quartile,
Value, Id
from @values
order by ExamId, Quartile
ntile works great when you really need a set number of buckets and each filled to approximately the same level. Notice that it would be trivial to separate these scores into percentiles by simply using ntile(100).
