Useful SQL - CTE Common Table Expressions

I was delighted when I discovered the power of CTEs, some years ago, as they have made many of my queries so much clearer since I started making use of this powerful syntax when required.

Basically, in one single SQL statement you can create one or more named temporary tables in a sequence, each able to reference the prior SELECTs. This works anywhere a single SQL statement can be issued, even in a report definition.

How about an example? You will usually query database tables of course, but here we hard-code some data for a stand-alone example requiring no other data :

With TestData as (
        SELECT 1 as id, 'Number 1' as txt
  UNION SELECT 2,'no 2'
  UNION SELECT 3,'3rd'
  UNION SELECT 4,'a 4th'
),
Largest as (
  SELECT Max(id) as Max_id FROM TestData
)
SELECT TestData.*
FROM TestData, Largest
WHERE id=Largest.Max_id

You can see this in action and play with it (MS SQL) with an example I've created at SQLFiddle.com (CTEs are also available in Oracle and PostgreSQL - but not in MySQL).

The temporary tables can be used as any other table, joins and all. If this is new to you, and you can appreciate from this the power and possibilities that this gives you, I'm sure you'll be thinking "Wow!". The CTE technique has saved my day numerous times when a simple SQL query simply couldn't do what I needed.

And then I found that you can make them recursive! Ever needed a sequential list of numbers? Here's a list from 1 to 10 :

With Numbers AS
(
 SELECT n = 1
   UNION ALL
 SELECT n + 1
 FROM Numbers
 WHERE n+1 <= 10
)
SELECT * FROM Numbers

( http://sqlfiddle.com/#!6/55030/12 )

That could be useful with an Outer Join if you want to visually find gaps in a sequence of row IDs, for example. It works like a For Loop in C, by specifying the start, the increment, and the final condition.

Here's one more, where we take the 2nd and 3rd rows from the hardcoded record set :

With TestData as (
        SELECT 1 as id, 'Number 1' as txt
  UNION SELECT 2,'no 2'
  UNION SELECT 3,'3rd'
  UNION SELECT 4,'a 4th'

),

Numbers as (
 SELECT n = 2
   UNION ALL
 SELECT n + 1
 FROM Numbers
 WHERE n+1 <= 3
)

SELECT * FROM TestData,Numbers
WHERE id=Numbers.n

( http://sqlfiddle.com/#!6/55030/10 )

Many complex problems on StackOverflow can be solved cleanly using the power of CTE.
HTH