Want to learn more on NTILE()

Question

I was reading on RANKING function for ms sql. I understand the others function except NTILE(). Lets say if i have this data:

   StudentID     MARKS         S1           75         S2           83       S3           91       S4           83       S5           93   

So if i do a NTILE(2) OVER(ORDER BY MARKS desc) what will be the result and why?
And what if it is a NTILE(3)? Simple explaination anyone?

Answer 1

Think of it as buckets, NTILE(2) will make 2 buckets, half the rows will have the value 1 and the other half the value 2

example

create table  #temp(StudentID char(2),    Marks  int)  insert #temp  values('S1',75 )  insert #temp  values('S2',83) insert #temp  values('S3',91) insert #temp  values('S4',83) insert #temp  values('S5',93 )    select NTILE(2) over(order by Marks),* from #temp order by Marks 

Here is the output, since you have an uneven number of rows, bucket 1 will have 1 row more

1   S1  75 1   S2  83 1   S4  83 2   S3  91 2   S5  93 

If you add one more row

insert #temp  values('S6',92 )  

Now both buckets have 3 rows

1   S1  75 1   S2  83 1   S4  83 2   S3  91 2   S6  92 2   S5  93 

In reality I have never used NTILE in production code but I can see the use where you need to split the results into n number of buckets