CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CTE

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-04 : 21:19:31

Can every CTE query can be converted to an equivalent subquery?

subhash chandra
Starting Member

40 Posts

Posted - 2010-03-05 : 08:43:16

Some CTE query can be written in alternate way using subquery, derived tables or temporary tables but can not necessarily be written by only using subquery. The speciality of CTE query is its recursive use and that needs much complex and lengthy code using other methods.

Regards,
Subhash Cnabdra
http://SQLReality.com/blog/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 09:36:23

recursive CTE require loop if you want them to be converted to subquery

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 12:07:12

Ok... My query is not recursive CTE.
It is somewhat similar to...
I want to find each top salaried emploee in a department
so now it is similar to

with A as
(select dept,max(sal) c from employee where dept='Accounts')
Select * from employee inner join a on
dept=a.dept and sal= a.c

I tried rewriting it as
Select * from employee where (dept,sal) in (select dept,max(sal) c from employee where dept='Accounts')
which fails... with 'Incorrect syntax near ','.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:15:14

for getting top salaried employee in department this is enough


SELECT relevant columns....
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY dept ORDER BY sal DESC) AS Seq
FROM employee
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:21:42

quote:
Originally posted by AAAV

Ok... My query is not recursive CTE.
It is somewhat similar to...
I want to find each top salaried emploee in a department
so now it is similar to

with A as
(select dept,max(sal) c from employee where dept='Accounts')
Select * from employee inner join a on
dept=a.dept and sal= a.c

I tried rewriting it as
Select * from employee where (dept,sal) in (select dept,max(sal) c from employee where dept='Accounts')
which fails... with 'Incorrect syntax near ','.

this is not a valid syntax in sql server

equivalent is


Select * from employee e where exists (select 1 from employee where dept=e.dept  group by dept having min(sal)=e.sal)

EDIT: removed unwanted in
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 12:41:08

Thanks
this is how i changed the CTE to a query...
truncate table temp_table2

Insert into Temp_table2
SELECT state, vc_trimid, vc_modelid, Year,
segment, manufacturer, make, model, series, body_style_name,
series_body_style_name, engine, trans, retained_value_percent_of_year5,
'Highest Resale Value After 5 Years in its Class' as class_leadership
FROM
(Select RANK() OVER(PARTITION BY state,year,segment ORDER BY retained_value_percent_of_year5 DESC) AS Sequence ,* from web_export)
temp_data where Sequence=1

drop table temp_table;

WITH A AS
(SELECT web_export.state, web_export.year, web_export.segment,
Max(web_export.retained_value_percent_of_year5) AS MaxOfretained_value_percent_of_year5
FROM web_export
GROUP BY web_export.state, web_export.year, web_export.segment)
SELECT web_export.state, web_export.vc_trimid, web_export.vc_modelid, web_export.Year,
web_export.segment, web_export.manufacturer, web_export.make, web_export.model, web_export.series, web_export.body_style_name,
web_export.series_body_style_name, web_export.engine, web_export.trans, web_export.retained_value_percent_of_year5, 'Highest Resale Value After 5 Years in its Class'
as class_leadership into Temp_table
FROM web_export INNER JOIN A ON
(web_export.year = A.year) AND
(web_export.state = A.state) AND
(web_export.retained_value_percent_of_year5 = A.MaxOfretained_value_percent_of_year5) AND
(web_export.segment = A.segment)

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 12:41:59

thanks for the other correction too...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:43:23

why you used RANK instead of ROW_NUMBER?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 12:48:09

Rank because of row number since there are more than one rows with that value and i want all the rows with that value.

just curious... tried to rewrite it with the exits... can you tell what is the incorrect syntax near 'in'

SELECT web_export.state, web_export.vc_trimid, web_export.vc_modelid, web_export.Year,
web_export.segment, web_export.manufacturer, web_export.make, web_export.model, web_export.series, web_export.body_style_name,
web_export.series_body_style_name, web_export.engine, web_export.trans, web_export.retained_value_percent_of_year5,
'Highest Resale Value After 5 Years in its Class' as class_leadership
FROM web_export A where exists (select 1 from web_export where year=A.year and state=A.state and segment=A.segment in
group by state,year,segment having Max(retained_value_percent_of_year5)=A.retained_value_percent_of_year5)
truncate table temp_table2

Why it fails in
(select 1 from web_export where year=A.year and state=A.state and segment=A.segment in
group by state,year,segment having Max(retained_value_percent_of_year5)=A.retained_value_percent_of_year5)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:52:32

oops it was a typo you dont need that in before group by. i've edited the main solution also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 12:54:52

super...
So now... which one will be more efficient in processing thousands of records? atleast 200,000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 12:55:41

i'm not near a sql box. can you do a test between now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 13:00:03

select 1 -SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 25 ms.

(250 row(s) affected)

RANK()- SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 41 ms.

(250 row(s) affected)

CTE-SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 14 ms.

(250 row(s) affected)

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-05 : 13:05:22

I am thinking to use the rank because i have to do this 13 times with min,max value for different costs and union all the results into a table...
with CTE i am not able to insert into...
everytime i am creating a temp table and dropping it after inserting its results to the final table.
when i use rank i am able to insert directly into the final table so i can avoid the drop create tables and the insertions

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 13:08:41

ok..that do make sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources