Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

CTE inside CTE in SQL Server

Mar 20 2012 12:00AM by Paresh Prajapati   

As per requirement in custom logic we need to require CTE (Common Table Expression) something like for the hierarchy, to find duplicate and remove data or for some other stuff. Recently i have used CTE within CTE for one logic and maxrecursion option as well.  So we will look as how we can use CTE inside CTE or multiple CTEs. Let us create one requirement. The requirement is we need to find the first and second objects by object types in the database and it should be in ascending order. 

The sample data will be created from the script below. Let us create it first then we will demonstrate for the logic that need to be created as per requirement.
USE DEMO
GO

-- Creating sample table
IF(OBJECT_ID('TblCTEwithCTE','U') > 0)
  DROP TABLE TblCTEwithCTE

CREATE TABLE TblCTEwithCTE
(
 ObjectNumber INT ,
 ObjectType VARCHAR(50),
 ObjectName VARCHAR(100),
 ObjectCreateDate DATETIME
)

GO

-- Inserting sample records created above
INSERT INTO TBLCTEWITHCTE
(
 ObjectNumber,
 ObjectType,
 ObjectName,
 ObjectCreateDate
)
SELECT 
 ROW_NUMBER() OVER(PARTITION BY TYPE_DESC ORDER BY TYPE_DESC,CREATE_DATE) as ObjectNumber,
 TYPE_DESC,
 NAME,
 CREATE_DATE 
FROM SYS.OBJECTS
Here we are creating first CTE to get only objectes with created first based or created date by object type categories.
-- Fetching first created objects
;WITH FirstCreatedObjectsCTE
AS
(
 SELECT 
  ObjectNumber as ObjectNumber ,
  ObjectType as ObjectType, 
  ObjectName as FirstCreatedObject
 FROM TBLCTEWITHCTE WHERE ObjectNumber = 1
) 

SELECT 
 * 
FROM FirstCreatedObjectsCTE

GO
CTEWithCTE_1_thumb1
 
Now we have completed to work for the first created objects by object type categories. And it is time to have the second created objects using first CTE and another second CTE to finally come out with output which having both first created and next created objects.

This first created and next created objects by object type categories will be shown as column as following.
-- Original table data.
SELECT 
 ObjectNumber,
 ObjectType,
 ObjectName,
 ObjectCreateDate
FROM TBLCTEWITHCTE
GO

-- Fetching first created objects in first CTE and using in second CTE for the second created objects.
;WITH FirstCreatedObjectsCTE
AS
(
 SELECT 
  ObjectNumber as ObjectNumber ,
  ObjectType as ObjectType, 
  ObjectName as FirstCreatedObject
 FROM TBLCTEWITHCTE WHERE ObjectNumber = 1
) 
,

SecondCreatedObjectsCTE
AS
(
 SELECT 
  t.ObjectType as ObjectType,
  c.FirstCreatedObject as FirstCreatedObject,
  t.ObjectName as SecondCreatedObject
 FROM TBLCTEWITHCTE t 
  RIGHT OUTER JOIN 
 FirstCreatedObjectsCTE c
  ON (c.ObjectType = t.ObjectType and t.ObjectNumber = c.ObjectNumber + 1)
)


SELECT 
 * 
FROM SecondCreatedObjectsCTE

GO
CTEWithCTE_2_thumb226
 
Hope you liked this, stay tuned from more.

Tags: sql, tsql, sql server, ms sql server, t-sql, #SQL Server, #sql, database, sql server general, SQL Scripts, backup, CTE, Common Table Expression


Paresh Prajapati
6 · 23% · 7475
2 Readers found this post to be a life saver
Paresh Prajapati found this post to be a life saver on 3/20/2012 5:18:00 AM
Profile · Blog · Facebook · Twitter
techkrg found this post to be a life saver on 4/8/2012 7:10:00 PM
Profile
9
 
2
Lifesaver
 
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"CTE inside CTE in SQL Server" rated 5 out of 5 by 9 readers
CTE inside CTE in SQL Server , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]