Common Table Expressions

Intermediate SQL Server

Ginger Grant

Instructor

CTE syntax

-- CTE definitions start with the keyword WITH 
-- Followed by the CTE names and the columns it contains
WITH CTEName (Col1, Col2)
AS  
-- Define the CTE query 
(  
-- The two columns from the definition above
    SELECT Col1, Col2 
    FROM TableName 
)

CTEs in T-SQL

-- Create a CTE to get the Maximum BloodPressure by Age
WITH BloodPressureAge(Age, MaxBloodPressure)
AS 
(SELECT Age, MAX(BloodPressure) AS MaxBloodPressure
 FROM Kidney
 GROUP BY Age)

-- Create a query to use the CTE as a table
SELECT a.Age, MIN(a.BloodPressure), b.MaxBloodPressure
FROM Kidney a
-- Join the CTE with the table  
JOIN BloodpressureAge b
     ON a.Age = b.Age
GROUP BY a.Age, b.MaxBloodPressure

Let's practice!

Intermediate SQL Server