Multiple CTEs in One Query

CTEs are a great thing to use in your query.  They are an excellent way to consolidate code and make it readable as well as create a “view” inside of your code.  One great thing I have used them for many times was to pick out the max date associated with a set of key values.  So let’s say there is a table that has a column called ID, Quantity and Date.  And in this table there are duplicate IDs with different quantities and dates associated with them.  So ID 4 could be in the table twice with a quantity of 4 and date of 1/1/2010 and also a quantity of 8 and a date of 2/8/2010.  We will then create a CTE with all the valid key and date values.
SELECT ID, MAX(Date) FROM Table
Next we can select all of our data from the table and limit it to one the records that exist in that CTE by using WHERE Exists on the condition of the ID and Date matching an entry in the CTE.
The question becomes what happens if you want to use multiple CTEs in a query.  I have wanted to do this before but had to find ways around it.  There has to be one of several operations that follows a CTE in the query; a select statement, update, insert, delete or merge. (at least I think those are the only three off the top of my head)  If I wanted to use more than one CTE I would have to declare one, use a select and then declare a second.  What if I want to use 2 CTEs in the same query?  Luckily there is a way to do this, and unfortunately I didn’t find this out until I finished that section of my project.  Needless to say this would have been much better and made my life a lot easier!  The basic syntax will be your normal CTE declaration, followed by a comma, followed by declaring the next CTE but leaving off the WITH.  Was that confusing enough without showing you how to do it?  Here is an example:
WITH Departments AS (Select * from HumanResources.Department),
EmployeeDepartments AS (Select * fromHumanResources.EmployeeDepartmentHistory)
SELECT *
FROM Departments d
INNER JOIN EmployeeDepartments ed ON d.DepartmentID = ed.DepartmentID
Notice where the first CTE ends and a comma is added then the second CTE name is given followed by the query for it.  Normally each of those, if not used in the same query, would be written like this:
First CTE: WITH Departments AS (Select * from HumanResources.Department)
Other CTE: WITH EmployeeDepartments AS (Select * fromHumanResources.EmployeeDepartmentHistory)
Just remember the following:
WITH CTEName AS (Query),
SecondCTEName AS (SecondQuery)
The rest of your query here

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me