Great. Just as you can have multiple columns in your CTE, you can also have multiple CTEs, some of which may be recursive, others not. The rule for syntax is as follows: if at least one CTE is recursive, start the whole query with
WITH RECURSIVE, and then define your CTEs just as you would normally do. Take a look at the example below.
WITH RECURSIVE prime(x) AS (
total(multiply,prime,score) AS (
x * (multiply+1)
FROM prime, total
WHERE multiply < 3
ORDER BY multiply, prime;
In the example, we first create a list of the first 5 prime numbers, and then we create a recursive CTE that calculates multiples of these prime numbers. Note that the word
RECURSIVE is always used before the first CTE, even if that query is not recursive.