Common Table Expression in SQL Server 2005

We published a new article today:
Working With Temporary Views Using Common Table Expression in SQL Server 2005
http://www.sql-server-performance.com/art_cte_sql2005.asp



Shane Tasker
--
Contributing Editor, SQL-Server-Performance.Com
Independent Consultant

Here is example of how to delete duplicate rows with duplicate FirstName,LastName columns from Customers
Using Common Table Expression (CTE) in SQL Server 2005:

-- remove duplicate rows using CTE and ROWNUMBER() in SQL2005

WITH DUPLICATE(FirstName,LastName, RowNumber)
AS
(
SELECT FirstName
,LastName
,ROW_NUMBER() OVER (PARTITION BY LastName, FirstName order by LastName, FirstName) AS RowNumber
FROM Customers
)
DELETE FROM DUPLICATE WHERE RowNumber > 1


Jiri JANECEK
MSE, MBA, MCSD.NET

Is it possible to update values in the columns returned by the CTE... as in case of Temporary Tables...???

I have been having issues with this query.

My table has

Category_ID <-Reporting to a Parent ID
Parent_ID
Category Name

The query I am using is a copy of yours
================================================

WITH Tree(Category_ID, Parent_ID, Category_Name, Hlevel)
AS
(
SELECT Category_ID, Parent_ID, Category_Name, 0 as Hlevel
FROM Dir_Categories
WHERE Parent_ID = 0
UNION ALL
SELECT e.Category_ID, e.Parent_ID, e.Category_Name, Hlevel + 1
FROM Dir_Categories e
INNER JOIN Tree d
ON e.Parent_ID = d.Category_ID
)
SELECT Category_ID, Parent_ID, HLevel,
Category_Name = Replicate('|__', HLevel) + Category_Name
FROM Tree ;
GO

Here is the output from HELL (Sorry, it's Killing me), I abreviated the headers below so it canfit better
It may look like a Tree View, but the subcategories are not inside their corresponding categories. HELP PLEASE I CANT SLEEP!!!
=======================================
CatID ParID Level Category Name
-----------------------------------------
100Arts & Humanities
200Business
300Children
400Computers
500Education
600Entertainment
700Home & Garden
800Health & Fitness
900News & Politics
1000People & Society
1100Real Estate
1200Recreation & Sports
1300Science & Technology
1400Shopping
1500Travel
28200Weblogs
50800Lifestyle & Relationships
53600Home & Family
5375361|__Food & Drink
5385361|__Family Entertainment
5325372|__|__Drinks and Drinking
5335372|__|__Alcoholic Beverages
5345372|__|__Non-Alcoholic Beverages
5355372|__|__Home Recipes
5095081|__Personal Development
5105081|__Alternative Lifestyle
5115081|__Cyber Relationships
5125081|__Dating
5135081|__Divorce
5145081|__Friendship
5155081|__Help and Advice
5165081|__Chats and Forums
5175081|__Love and Romance
5185081|__Marriage
5195081|__Weddings
5205081|__Singles
2832821|__Business Blogs
2842821|__Celebrity Blogs
2852821|__Entertainment Blogs
2862821|__Media Blogs
2872821|__Personal Blogs
2882821|__Political Blogs
2892821|__SfiFi Blogs
2902821|__Travel Blogs
2912821|__Sports Blogs
2922821|__Weblog Awards
2932821|__Weblog Directories
2942821|__Weblog Hosting
2952821|__Weblog Tools
5482821|__SEO & Web Marketing Blogs
272151|__Accommodations
273151|__Agents and Services
274151|__Budget Travel
275151|__Business Travels
276151|__Destination Guides
277151|__Trip Planning
278151|__Maps
279151|__News and Media
280151|__Transportation
281151|__Travelogues
238141|__Apparell
239141|__Arts & Collectibles
240141|__Auctions
241141|__Babies
242141|__Beauty
243141|__Books and Magazines
245141|__Business Supplies
246141|__Beauty Supplies
247141|__Children
248141|__Classified Ads
249141|__Comparison Shopping
250141|__Computers
252141|__Consumer Electronics
253141|__Consumer Information
254141|__Flowers
255141|__Food and Drink
256141|__Freebies
257141|__Furniture
258141|__Garden
259141|__General Merchandise
260141|__Gifts and Holidays
261141|__Health and Fitness
262141|__Hobbies and Crafts
263141|__Home
264141|__Jewelry
265141|__Membership Waterhouse Clubs
266141|__Outdoors
267141|__Wedding
269141|__Vehicles
268141|__Religious Products
270141|__Toys and Games
271141|__Other
236131|__Science
237131|__Technology
235121|__Athlete Information
233121|__Recreation
234121|__Sports
5392342|__|__Golf
5402342|__|__Basketball
5412342|__|__Baseball
5422342|__|__Sport Collectibles
5432342|__|__Football
5452342|__|__Soccer
5462342|__|__Sport Fan Clubs
5472342|__|__News & Publications
5632342|__|__Fishing
5712342|__|__Air Sports
5702342|__|__Tennis
5742342|__|__Horse Racing
216111|__Agents
217111|__Appraisers
218111|__Brokers
219111|__Commercial
220111|__Construction
221111|__Consultants
222111|__Foreclosures
223111|__For Sale by Owner
224111|__Home Builders
225111|__Inspectors
226111|__Legal
227111|__Masterplan Communities
228111|__Property Search
229111|__Relocation Services
230111|__Schools and Courses
231111|__Rentals
232111|__Buying or Selling a Home
204101|__Communities and Groups
205101|__Connecting With People
206101|__Cultures
207101|__Genealogy
208101|__Military
209101|__Off The Beaten Path
210101|__Who is Who
211101|__Spirituality and Religion
212101|__Society
213101|__Men
214101|__Women
215101|__Youth
19391|__Aggregators and Summaries
19491|__News
19591|__Politics
19691|__Ezines
19791|__Internet Broadcast
19891|__Nespapers and Periodicals
19991|__Services
20091|__Community
20191|__Education
20291|__Television
20391|__Weather
16981|__Aging
17081|__Alternative Medicine
17181|__Beauty
17281|__Child Health
17381|__Dental Health
17481|__Dieting and Weight Loss
17581|__Diseases and Desorders
17681|__Drugs and Medicines
17781|__Health News
17881|__Health Professions
17981|__Hospitals
18081|__Fitness and Exercise
18181|__Education
18281|__Medical Organizations
18381|__Medical Practice
18481|__Medical Specialists
18581|__Public Health
18681|__Nutrition
18781|__Procedures and Therapies
18981|__Women Health
19081|__Men Health
19181|__Injuries
19281|__Sexual Health
16271|__Arts and Craft
16371|__Do It Yourself
16471|__Gardening
16571|__Homemaking
16671|__Interior Design
16771|__Pest Control
16871|__Pets and Animals
56671|__Home Improvement
55071|__Home Security
56971|__Home Safety & Prevention
56571|__Flooring
15061|__Entertainment Reviews
15161|__Celebrities
15261|__Animation
15361|__Games
15461|__Gossip
15561|__Horror
15661|__Humor
15761|__Interactive Websites
15861|__Movies
15961|__Music
16061|__Radio
16161|__Television
13351|__Adult Education
13451|__Directories
13551|__Distance Learning
13651|__Early Education
13751|__Educational Software
13851|__Fields of Study
13951|__Financial Aid
14051|__Graduation
14151|__Home Schooling
14251|__Internships
14351|__K-12_Education
14451|__Tests and Testing
14551|__Libraries and Resources
14651|__Educational Material
14751|__References
14851|__Schools
14951|__General Resources
13041|__Internet
13141|__Intranet
13241|__Networking
11041|__Algorithms
11141|__Artificial Intelligence
11241|__Artificial Life
11341|__Computer Jobs
11441|__CAD
11541|__Companies
11641|__Graphics
11741|__Education
11841|__Data Communications
11941|__Hacking
12041|__Hardware
12141|__Software
12241|__Consulting
12341|__Support and Repair
12441|__Multimedia
12541|__Publications
12641|__Virtual Reality
12741|__Robotics
12841|__Security and Protection
12941|__Tutorials and Guides
4711122|__|__Agents
4721122|__|__Art
4731122|__|__Artificial Words
4741122|__|__Biomimicry
4761122|__|__Conferences and Events
4771122|__|__Departments
4781122|__|__Distributed Projects
4791122|__|__Genetic Programming
4801122|__|__Iterate Prisoner Dilemma
4811122|__|__Lindenmayer Systems
4821122|__|__Natural Language
4831122|__|__Particle Swarms
4841122|__|__Programs and Applets
4851122|__|__Research Papers
4561112|__|__Conferences and Events
4521112|__|__Academic Departments
4531112|__|__Agents
4581112|__|__Data Mining
4591112|__|__Distributed Projects
4601112|__|__Fuzzy
4611112|__|__Games
4621112|__|__Knowledge Representation
4631112|__|__Machine Learning
4641112|__|__Natural Language
4651112|__|__Natural Networks
4661112|__|__Philosophy
4671112|__|__Programming Languages
4681112|__|__Publications
4691112|__|__Qualitative Physics
4701112|__|__Vision
4541112|__|__Applications
4551112|__|__Belief Networks
4751112|__|__Cellular Automata
4571112|__|__Creativity
4401102|__|__Animated
4411102|__|__Compression
4421102|__|__Computational Algebra
4431102|__|__Cryptography
4441102|__|__Digital Signal Processing
4451102|__|__Genetic
4501102|__|__Pseudorandom Numbers
4511102|__|__Speech Recognition
5621302|__|__Web Hosting Services
5681302|__|__Search Engine Optimization
5641302|__|__Web Design
10731|__Nutrition
10831|__Children Books
10931|__Computer Related
10031|__Toys
10131|__Family Entertainment
10231|__Health
10331|__Pets and Animals
10431|__Recreation
10531|__Schools
10631|__Sports
56031|__Parenting
5051022|__|__Exercise
5061022|__|__Food and Nutrition
5071022|__|__Safety
4971012|__|__Arts and Crafts
4981012|__|__Humor
4991012|__|__Dance
5001012|__|__Movies
5011012|__|__Music
5021012|__|__Theater
5031012|__|__Toys
5041012|__|__Magazines
5311012|__|__Food and Drink
4891092|__|__Computer and Video Games
4901092|__|__Computer Science
4911092|__|__Directories for Kids
4921092|__|__Home Pages
4931092|__|__Online Games
4941092|__|__Pages for Kids
4951092|__|__Software
4961092|__|__Web Design
8621|__Comodities
8721|__Computing
8821|__Finance
8921|__Global Commerce and Government
9021|__Human Resources
9221|__Industry Specific
9321|__Law
9421|__Management
9521|__News and Resources
9621|__Non Profit
9721|__Professional Associations and Organizations
9821|__Sales and Marketing
9921|__Small Business
54921|__Insurance
57221|__Business Services and Resources
5735722|__|__Investigation Services
427982|__|__Advertising
428982|__|__Associations
429982|__|__Consulting Services
430982|__|__Corporate Branding
431982|__|__Direct Marketing
432982|__|__Online Marketing
433982|__|__Product Development
434982|__|__Promotional Marketing
435982|__|__Public Relations
436982|__|__Sales
437982|__|__Segmentation and Targeting
438982|__|__Specialized by Industry
439982|__|__Strategic Planning
416962|__|__Animal Welfare
417962|__|__Charitable Organizations
418962|__|__Childrens Organizations
419962|__|__Consulting Services
420962|__|__Environmental Organizations
421962|__|__Fundraising
422962|__|__Global Aid
423962|__|__Health Organizations
424962|__|__Religious Organizations
425962|__|__Resources
426962|__|__Volunteer Opportunities
406952|__|__Business Columnists
407952|__|__Business Newspapers
408952|__|__Business Portals and Directories
409952|__|__Investment News
410952|__|__Mailing Lists
411952|__|__Online Publications and Journals
412952|__|__Radio and TV Programs
413952|__|__Regional News Sources
414952|__|__Road Conditions
415952|__|__Sales Leads
393942|__|__Associations
394942|__|__Benchmarking
395942|__|__Business Ethics
396942|__|__Business Services and Resources
397942|__|__Corporate Governance
398942|__|__Information
399942|__|__Knowledge Management
400942|__|__Management Theory
401942|__|__News and Publications
402942|__|__Operations Management
403942|__|__Organization Development
404942|__|__Research
405942|__|__Training and Education
365932|__|__Associations
366932|__|__Branches of Law
367932|__|__International Law
368932|__|__Legal Professionals
369932|__|__Legal Resources
370932|__|__Legal Services
371932|__|__News and Publications
3723663|__|__|__Administrative
3733663|__|__|__Bankruptcy
3743663|__|__|__Business
3753663|__|__|__Communication
3763663|__|__|__Constitutional
3773663|__|__|__Consumer
3783663|__|__|__Criminal Law
3793663|__|__|__Cyberspace
3803663|__|__|__Disabilities
3813663|__|__|__Personal Injury
3823663|__|__|__Employment
3833663|__|__|__Entertainment
3843663|__|__|__Environmental Law
3853663|__|__|__Family Law
3863663|__|__|__Immigration
3873663|__|__|__Intellectual Property
3883663|__|__|__Product Liability
3893663|__|__|__Real Estate
3903663|__|__|__Traffic Violations
3913663|__|__|__Trust and Estate Law
3923663|__|__|__Benefits
353922|__|__Aerospace and Military
354922|__|__Agriculture
355922|__|__Construction
356922|__|__Energy and Utilities
357922|__|__Manufacturing
358922|__|__Media and Entertainment
359922|__|__Mining and Drilling
360922|__|__Retail
361922|__|__Science and Technology
521922|__|__Automotive
362922|__|__Service Industry
363922|__|__Transportation and Logistics
364922|__|__Wholesale Trade
5225213|__|__|__Repair and Maintenance
5235213|__|__|__Exotic and Classic Vehicles
5245213|__|__|__Racing
5255213|__|__|__Diesel Mechanics
5265213|__|__|__Auto Sales
5275213|__|__|__Road Services
5285213|__|__|__General Enhancements
5295213|__|__|__Car Accessories
5305213|__|__|__High Performance
340902|__|__Career Management
341902|__|__Compensation
342902|__|__Consulting Services
343902|__|__Employee Evaluation
344902|__|__Employee Testing
345902|__|__Employment and Career
346902|__|__Outsourcing
347902|__|__Recruitment
348902|__|__Relocation
349902|__|__Retirement Benefits and Services
350902|__|__Special Benefits and Services
351902|__|__Training and Education
352902|__|__Workplace Health and Safety
324892|__|__B2B Markets
325892|__|__Business Guides
326892|__|__Chambers of Commerce
327892|__|__Consulting Services
328892|__|__Export Assistance
329892|__|__Foreign Trade Zone
330892|__|__Government
331892|__|__Importers and Exporters
332892|__|__International Taxation
333892|__|__ISO
334892|__|__Localization
335892|__|__News and Publications
336892|__|__Trade Development
337892|__|__Trade Leads
338892|__|__Translation Services
339892|__|__World Trade Centers
313882|__|__Banking
314882|__|__Business Finance
315882|__|__Business Insurance
316882|__|__Debt Collection
317882|__|__Consumer Finance
318882|__|__Financial Services
319882|__|__Investing
320882|__|__Mortgage
322882|__|__Taxes
323882|__|__Venture Capital
309872|__|__Associations
310872|__|__Computers
311872|__|__Information Technology
312872|__|__Internet
5613123|__|__|__Web Hosting Services
296862|__|__Automated Teller Machines
297862|__|__Binders and Laminating
298862|__|__Communication
299862|__|__Office Space
300862|__|__Packaging and Containers
301862|__|__Product Tracking
302862|__|__Record Storage
303862|__|__Signage
304862|__|__Stationary Supplies
305862|__|__Time Recording and Management
1611|__Books and Literature
1711|__Philosophy
1811|__Antiques
1911|__Arts
2011|__Humanities
2111|__Museums
2211|__Organizations
56711|__Crafts
84222|__|__National Endowment for the Arts
85222|__|__National Endowment for the Humanities
78212|__|__Maritime Museums
79212|__|__Railway Museums
80212|__|__Aviation Museums
81212|__|__Children Museums
82212|__|__Science Museums
83212|__|__Museums
71202|__|__Books and Literature
72202|__|__Classical Studies
73202|__|__Cultural Studies
74202|__|__History
75202|__|__Languages
76202|__|__Linguistics
77202|__|__Philosophy
60192|__|__Animation
61192|__|__Arts Publications
62192|__|__Arts Resources
63192|__|__Art Exhibits
64192|__|__Cartoons
65192|__|__Design Arts
66192|__|__Education
67192|__|__Museums
68192|__|__Performing Arts
69192|__|__Visual Arts
70192|__|__Writers Resources
58182|__|__Antique Jewelry
59182|__|__Restoration
35172|__|__Academic Departments
36172|__|__Ancient Greek Philosophy
37172|__|__Axiology
38172|__|__Chats and Forums
39172|__|__Chinese Philosophy
40172|__|__Continental Philosophy
41172|__|__Current Movements
42172|__|__Eastern
43172|__|__Epistemology
44172|__|__Ethics
45172|__|__German Idealism
46172|__|__History
47172|__|__Humanism
48172|__|__Journals
49172|__|__Logic
50172|__|__Metaphysics
51172|__|__New Age
52172|__|__Organizations
53172|__|__Personal Pages
54172|__|__Philosophers
55172|__|__Philosophy of Religion
56172|__|__Philosophy of Science
57172|__|__Reference
23162|__|__Art Books
24162|__|__Authors
25162|__|__Banned Books
26162|__|__Best-Seller Lists
27162|__|__Bookstores and Booksellers
28162|__|__Book Reading Groups
29162|__|__Downloadable Texts
30162|__|__By Genre
31162|__|__Magazines and e-Zines
32162|__|__Periods and Movements
33162|__|__Publishers
34162|__|__Reviews

udelojf, please start a new thread for your question.

Madhivanan, this is an old thread!