NTILE

  • 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]

  • 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"

  • 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]

  • 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

  • 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)

  • 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]

  • 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

  • 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]

  • ntile(33)? :laugh:

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

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