[ 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.