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

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-22 : 06:23:46

Hey any one help me to learn CTE table with very simple examples.

Raghu' S

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-22 : 07:21:32

a common use of CTE is to remove duplicate values like following:

CREATE TABLE #TestDuplicate (intVAl INT)
GO
INSERT INTO #TestDuplicate
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 3
GO
SELECT * FROM #TestDuplicate
-- THERE ARE DUPLICATE VALUES, LETS REMOVE THEM USING CTE

;WITH MyCTE (intVAl,RowNumber)
AS (SELECT intVAl,ROW_NUMBER() OVER (PARTITION BY intVAl ORDER BY intVAl) AS RowNumber FROM #TestDuplicate)

--SELECT * FROM MyCTE
--WHERE RowNumber =1

DELETE FROM MyCTE
WHERE RowNumber >1

--------------------------
http://connectsql.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 07:23:51

Books On Line is a great place to start.

Jim

Everyday I learn something that somebody else already knew

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-22 : 07:26:48

Hey actually we deleted from MyCTE table but how it effect on #TestDuplicate Table

Raghu' S

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 07:40:28

Look up SELECT and FROM in Books On Line
SELECT *
FROM #TestDuplicate

run this after
DELETE FROM MyCTE
WHERE RowNumber >1

to see what the table looks like

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-22 : 08:38:05

When I looked at CTE's the first time, I said to myself "Meh! What is the big deal!! Nothing more than glorified sub-queries. Why did they waste time on this when everything you want to do can be done with sub-queries or temp tables or table variables!!"

But, CTE's show their true colors when you use them in recursive contexts. Probably the simplest recursive CTE is

;WITH CTE AS
(
   SELECT 1 AS N
   UNION ALL
   SELECT N+1 FROM CTE WHERE N < 50
) SELECT * FROM CTE

Ta-da!! An instant table of numbers!! Notice how the CTE is being used inside the CTE definition itself.

You can do a whole lot more with CTEs. There are some examples here: http://msdn.microsoft.com/en-us/library/ms186243.aspx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-22 : 09:42:14

You can also generate numbers without recursion, if you stack your CTEs:

;WITH 
a(a) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
b(b) AS (SELECT 0 FROM a CROSS JOIN a x CROSS JOIN a y),
c(c) AS (SELECT 0 FROM b CROSS JOIN b x CROSS JOIN b y),
d(d) AS (SELECT ROW_NUMBER() OVER (ORDER BY c.c) FROM c)
SELECT * FROM d

This can get around the max recursion limit of 32767 in case you ever need to.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-22 : 10:07:15

Hey
You people really rock!!!!!!!!!!!!!!

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources