2 SELECT statements from one CTE

Question

2 SELECT statements from one CTE

sql_learner29

SSCrazy Eights

Points: 9107
More actions
November 2, 2010 at 1:22 pm

#231192

Hi,
Can I have two select statements for one CTE.
something like this:
declare @counter int;
with cte1
as
(
select a,b,c,level
from abcd
)
select @counter = MAX([LEVEL]) FROM cte1
select a,b,c from cte1
where Level = @counter

Any help?
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 15 posts - 1 through 15 (of 23 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

a CTE is available just for the next statement in the query, so if it really needed to exist twice, you'll need a temp table.

You're not really doing anything that really requires two selects; with the example you provided, it can all be done in a single statement:

declare @counter int;

with cte1

as

(

select a,b,c,level

from abcd

)

select a,b,c from cte1

where [Level] IN(SELECT MAX([LEVEL]) FROM cte1 )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

You don't need more than 1 select to solve this. Try this...

with cte1

as

(

select a,b,c,level

from abcd

)

select a,b,c from cte1

where Level = (select MAX([LEVEL]) FROM cte1 )

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Lowell beat me to the punch again. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 4

SeanLange (11/2/2010)
Lowell beat me to the punch again. 😛

lol happens way to often lately!

type faster! i guess it's a race now.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

Thanks but, I cannot use it in a subquery as I have to store the Level value in a variable and then have to add a LOOP to generate statements until a condition is satisfied.

declare @counter int;

select @counter = MAX([LEVEL]) FROM cte1

while @counter < 20

begin

select * from mytable

where Level = @counter

set @counter = @counter + 1

end

I cannot do this using CTEs ?

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Learner1 (11/2/2010)
Thanks but, I cannot use it in a subquery as I have to store the Level value in a variable and then have to add a LOOP to generate statements until a condition is satisfied.
declare @counter int;
select @counter = MAX([LEVEL]) FROM cte1
while @counter < 20
begin
select * from mytable
where Level = @counter
set @counter = @counter + 1
end
I cannot do this using CTEs ?

If you post some details we can probably help but once I hear loop I start cringing. What you posted is nothing more than an order by clause so I am assuming there is something more complex required. I am 99.99999999999% certain that a set based approach will get you to where you need to be instead of looping.

Post some table definitions, data imports and desired results. There are tons of people here willing to help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 7

Nope; that's a limitation of a CTE; it can be used only for one statement.

if you get rid of the pseudocode, and show us what you are actually doing, we can probably help you find a solution that never uses a loop at all;

it's just learning how to think in a more set base dmanner instead of a row-by row processing that you learn in programming languages.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 8

ok then let me create some test insert statement, I will reply soon

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Lowell SSC Guru Points: 323518 More actions · Answer 9

eek! SeanLange beat me to the punch on this one! you got some payback on that one, way to go Sean.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95461 More actions · Answer 10

Learner1 (11/2/2010)
Thanks but, I cannot use it in a subquery as I have to store the Level value in a variable and then have to add a LOOP to generate statements until a condition is satisfied.
declare @counter int;
select @counter = MAX([LEVEL]) FROM cte1
while @counter < 20
begin
select * from mytable
where Level = @counter
set @counter = @counter + 1
end
I cannot do this using CTEs ?

Use a tally table:

SELECT *

FROM mytable mt

JOIN tally t

ON t.N BETWEEN mt.Level AND 20

Not sure what a tally table is? See The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

😎 current score 1 - 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

WayneS SSC Guru Points: 95461 More actions · Answer 12

SeanLange (11/2/2010)
... There are tons of people here willing to help.

Are you looking at my picture and extrapolating? :-D;-):w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

LOL (irl). I can't count how often I laugh at your picture. Sadly it is how I feel today. Unfortunately it is not SQL, it is the dreaded evil browser IE that is causing me fits.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 14

Hi,

Creating test data is becoming problematic as its getting very confusing.

Let me explain the complete scenario first :

(Please be patient..it will go long)

We have a details table from where I am getting the base data.

My task is to create a Summary table in such a way to determine employee Performance based on search on his SUPERVISOR name.

From details table I get EmplNAME/ and his ACCOUNT_BALANCE

Now I add a LEVEL column (counter) to these two columns and find the EmplNAME's SUPERVISOR using a join with Employee table and sum up the account balance for that supervisor and store them in one CTE.

Now I have to run a loop for this CTE and find this SUPERVISOR's SUPERVISOR ....I am running loop until 10 times as I know that after 10 it will reach the CEO and that CEO will have no SUPERVISOR so it will return nothing.

So now after doing this kind of load, if I run my query for CEO it should show result like this:

SUPERVISOR_NAME CEO_EMP1_NAME ACCOUNT_BAL

CEO EMP1 100

CEO EMP2 200

CEO EMP3 300

CEO EMP4 400

This is basically what I want to achieve from this CTE, Is there any way to handle this requirement without complex looping.?

Let me know if you are not clear...

Any help?

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]