[ Ntile function in SQL Server, unexpected output ]

I am using SQL Server 2012

The query is:

create table #t (id int)

insert into #t values(1), (2), (3), (4), (5)

select id, ntile(2) over (order by id desc) 
from #t

Result:

id  (No column name)  (Expected was)
-------------------------------------
5   1                  1
4   1                  1
3   1                  2
2   2                  2
1   2                  3

I was expecting the 3 column result in SQL Server, why is the behavior of it different?

Ntile(2) should give 2 number to each?

Answer 1

From NTILE technet documentation:

For each row, NTILE returns the number of the group to which the row belongs.

NTILE(2) creates two groups of data, hence the number returned can only be in (1,2).

You seem to think that NTILE returns the position of each row within each group, which is clearly not the case.

If you use NTILE(3) instead, then 3 groups of data will be created, i.e.

 1. `{5, 4}` -> 1st group, 
 2. `{3, 2}` -> 2nd group and
 3. `{1}`    -> 3rd group.