Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select from a cte Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 5:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 286, Visits: 573
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


Post #1341150
Posted Tuesday, August 7, 2012 5:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
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
Post #1341152
Posted Tuesday, August 7, 2012 5:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 286, Visits: 573
Thanks a lot

spot on
Post #1341156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse