NTILE

Question

NTILE

Dave Ballantyne

SSC-Dedicated

Points: 33667
More actions
May 9, 2010 at 6:53 am

#221309

BOL States ( http://msdn.microsoft.com/en-us/library/ms175126.aspx )the syntax of NTILE as
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
And continues
integer_expression can only reference columns in the PARTITION BY clause. integer_expression cannot reference columns listed in the current FROM clause.
So that being the case, shouldnt this code be fine ?
with ctetest
as
(
Select 1 as col1,
2 as col2
)
select ntile(col1) over ( partition by col1 order by col1)
from ctetest
instead it errors with
Invalid column name 'col1'
Anyone tried to do something similar , is this a BOL error ?
Am i being stupid ? 🙂

Clear Sky SQL
My Blog[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

The argument for NTILE function is an integer, not a column value. NTILE(4) OVER (PARTITION BY Col1 ORDER BY Col1) will work.

N 56°04'39.16"
E 12°55'05.25"

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Agreed. Normally i would use NTILE with a constant value, but in this case i was playing around try to avoid a sort 'downstream' where i was using an existing row_number to effectively do an NTILE.

If a column cannot be used , why mention the Partition clause ? It seems superfluous (and wrong)

In any case i think ill throw a connect item up for clarification.

(Much easier without 140 char limitiation :))

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

Hey Dave,

1. integer_expression can only reference columns in the PARTITION BY clause

2. integer_expression cannot reference columns listed in the current FROM clause.

...but the documentation for PARTITION BY value_expression (OVER clause) says:

"Specifies the column by which the rowset produced by the corresponding FROM clause is partitioned. value_expression can only refer to columns made available by the FROM clause. value_expression cannot refer to expressions or aliases in the select list. value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable."

That seems entirely contradictory to me, probably a documentation bug, or maybe a feature that was documented but never implemented?

I originally thought you were just playing around with the NTILE syntax, but it seems there is some method to your madness...can you give some more details about what you are trying to do? Don't worry about it if you were just after a specific answer to your original question.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

On reflection, I still can't make sense of it, but I can demo an outer column reference being used both in the partition by and ntile:

declare @a table (a int null, b int null)

insert @a values (1, 1), (1, 2), (2, 3), (2, 4);

declare @b-2 table (groups int null)

insert @b-2 values (3), (2)

select *

from @b-2 outside

cross

apply (

select *,

group_id = ntile(outside.groups) over (partition by outside.groups order by a.a)

from @a a

) itvf;

(Code does nothing useful - just for demo)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Hi Paul

As i say im trying to avoid a sort further downstream , which is happening due to using the result of a row_number to effectively do an NTILE. So something like this...

with cteRows

as(

Select col1,row_number() over( partition by acol,bcol order by ...) as Rown

from table

),

cteX

as(

select col,RowN /3,RowN+1 %3 as ntilesub

from cteRows

)

Select * from cteX

order by col,ntilesub

So that caused two sorts.

By using NTILE at the same time as the row_number i was hoping to get one sort.

The problem then is , i dont care how many 'tiles', i have i know that i need a maximum of 3 on each.

So i was hoping to pass in a (count /3) +1 to NTILE.

Not business critical , just playing 🙂

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 6

On a quick inspection, wouldn't the following avoid the extra sort?

with cteRows

as (

Select col1,

row_number() over( partition by acol,bcol order by ...) as Rown,

ntile(3) over( partition by acol,bcol order by ...) as GroupID,

from table

)Rows

)

Select * from cteX

order by col, GroupID

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

Its not the same ,

That would split 99 rows into 3 tiles each containing 33 rows,

what i was after would split the 99 rows onto 33 tiles.

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 8

ntile(33)? :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi