How to group in fixed count ranges?

Let's say I have one table with tw开发者_Python百科o columns (student_id,grade).

student_id is an integer and grade is a decimal number.

In case I have 1000 students and want to group them ordered by grade in groups of 10 students each.

Just to be clear, this should produce 100 groups. The first group cointains the 10 highest grades and the last group contains the 10 lowest grades.

How should I do that ? Optimization is always welcome.

Thank you very much.

Joao

ntile will give a ranking by an amount of buckets.

select student_id, ntile(100) over (order by grade desc) from student