# Does NTILE work over floating point numbers?

I am trying to use NTILE on my data using a float data type in my PARTITION BY clause. Please excuse the contrived example, but I think the best way to illustrate my question and confusion is through this code:

``````CREATE TABLE Test
(
Cash float NOT NULL,
Number int NOT NULL
)
GO

INSERT INTO TEST VALUES(1.05,1);
INSERT INTO TEST VALUES(1.368,1);
INSERT INTO TEST VALUES(0.775,1);
INSERT INTO TEST VALUES(1.699,1);
INSERT INTO TEST VALUES(1.599,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(0.994,1);
INSERT INTO TEST VALUES(0.848,1);
INSERT INTO TEST VALUES(0.675,1);
INSERT INTO TEST VALUES(0.575,1);
INSERT INTO TEST VALUES(12.998,1);
INSERT INTO TEST VALUES(1.999,1);
INSERT INTO TEST VALUES(0.65,1);
INSERT INTO TEST VALUES(0.80,1);
INSERT INTO TEST VALUES(2.60,1);

SELECT CASH,
NTILE(3) OVER (PARTITION BY Cash ORDER BY Cash) AS Trio
INTO #Test
FROM Test
``````

I would have expected the groupings to be something like this:

``````0.575   1
0.65    1
0.675   1
0.775   1
0.8     1
0.8     2
0.8     2
0.848   2
0.994   2
1.05    2
1.368   3
1.599   3
1.699   3
1.999   3
2.6     3
12.998  3
``````

But instead, The result set looks like this:

``````CASH    Trio
0.575   1
0.65    1
0.675   1
0.775   1
0.8     1
0.8     2
0.8     3
0.848   1
0.994   1
1.05    1
1.368   1
1.599   1
1.699   1
1.999   1
2.6     1
12.998  1
``````

And the range groupings look really strange when max and min are applied by group

``````SELECT
MAX(CASH), MIN(CASH), Trio
FROM #Test
GROUP BY Trio

MAX     MIN     TRIO
12.998  0.575   1
0.8     0.8     2
0.8     0.8     3
``````

Can anyone explain what is going on here?

The `NTILE` function breaks an input set down into `N equal` sized groups. To determine how many rows belong in each group, SQL Server must first determine the total number of rows in the input set.

If the NTILE function includes a `PARTITION BY` clause, SQL Server must compute the number of rows in each partition separately. Once we know the number of rows in each partition, we can write the NTILE function as

``````NTILE(N) := (N * (ROW_NUMBER() - 1) / COUNT(*)) + 1
``````

where `COUNT(*)` is the number of rows in each partition.

Taking above into consideration, below will explain :

``````select *, (3*(RowNumber-1)/Cnt)+1 AS MyNTile
from
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Cash ORDER BY cash) AS RowNumber,
COUNT(*) OVER (PARTITION BY Cash ) AS Cnt,
NTILE(3) OVER (PARTITION BY Cash ORDER BY Cash) AS NTile,
NTILE(3) OVER (ORDER BY Cash) AS WholeTableNtile

FROM Test
) T
``````

• Thank you for not just answering the question, but explaining it as well. Sometimes in the midst of a problem, the obvious explanation escapes and your explanation is both clear and concise! – Thronk Apr 29 '15 at 1:58