Select from a cte

  • Hi

    I have a CTE statement i want to select from, I know there is the option of using multiple CTEs with a comma between but I dont think that that will solve my problem

    heres an example of the kind of thing i would like to do

    select one, two, rank(one) over (partition by two) as rank

    from

    (

    with cte as (

    select 1 as one, 2 as two from oneTable

    ) select one, two from cte

    union select one + 1 as one, two as two from cte

    union select one + 2 as one, two as two from cte;

    )

    Thanks in advance

  • You select from the cte at the end, not the beginning:

    with cte as

    (

    select 1 as one, 2 as two from oneTable

    ),

    cte2 as

    (

    select one, two from cte

    union select one + 1 as one, two as two from cte

    union select one + 2 as one, two as two from cte

    )

    select one, two, rank(one) over (partition by two) as rank

    from cte2

  • Thanks a lot

    spot on

Viewing 3 posts - 1 through 2 (of 2 total)

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