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 07, 2012 5:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 4:42 AM
Points: 278, Visits: 551
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 07, 2012 5:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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 07, 2012 5:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 4:42 AM
Points: 278, Visits: 551
Thanks a lot

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

Add to briefcase

Permissions Expand / Collapse