SQL Server 2005 : Mutliple Common Table Expressions (CTE)

Couple of hints using one of my favorite features of SQL server 2005 - Common table expressions. If you find this of use, leave a comment :)

USING MULTIPLE CTES IN A SINGLE SELECT STATEMENT
If you want to use more than one CTE, and let's face it - who wouldnt? Simply drop the WITH when adding the definition of the second (or more) CTE as shown below:

;with cte1 as
(select getdate() as field)
,cte2 as
(select getdate() as field)

select *
from cte1
inner join cte2 on cte1.field = cte2.field

CREATING A CTE BASED UPON ANOTHER CTE
If you dont like to have a derived table joined to the CTE, you might want to create a second CTE based on the first, here is how it can be done. Note that cte3 is just a select of CTE1 - simple but effective:

;with cte1 as
(select getdate() as field)
,cte2 as
(select getdate() as field)
,cte3 as
(select field as field
from cte1)

select *
from cte1
inner join cte2 on cte1.field = cte2.field
inner join cte3 on cte1.field = cte3.field

Enjoy!