Recursive CTE Example: calculate the factorial of a number

Calculating the factorial of a number using a recursive CTE in SQL can be both an instructive and practical example. Here’s a detailed step-by-step guide to achieve this.

Step-by-Step Explanation

1. Anchor Member: The base case of the recursion. For the factorial calculation, the base case is 0! = 1 or 1! = 1.
2. Recursive Member: Defines the recursive step. For the factorial of n, we have n! = n * (n-1)!.

Here's the SQL code to calculate the factorial of a number (let's say 5):

SQL Code

sql
WITH RECURSIVE FactorialCTE AS (
    -- Anchor member: start with 1
    SELECT 
        1 AS n, 
        1 AS factorial
    UNION ALL
    -- Recursive member: calculate factorial for n + 1
    SELECT 
        n + 1,
        factorial * (n + 1)
    FROM 
        FactorialCTE
    WHERE 
        n < 5 --Set the Dynamic limit to the desired number, in this case, 5
)
SELECT 
    n, 
    factorial
FROM 
    FactorialCTE
ORDER BY 
    n;

Explanation

1. Anchor Member:

   sql
   SELECT 
       1 AS n, 
       1 AS factorial
   This initializes the recursion with 1! = 1.

2. Recursive Member:

   sql
   SELECT 
       n + 1,
       factorial * (n + 1)
   FROM 
       FactorialCTE
   WHERE 
       n < 5
   
   

   This part of the CTE calculates the factorial by multiplying the current value of factorial by n + 1 until n reaches 5.

3. Final Select:

   sql
   SELECT 
       n, 
       factorial
   FROM 
       FactorialCTE
   ORDER BY 
       n;
   
   

   This selects and orders the results by n.

Result

The result will be a table listing each n from 1 to 5 and the corresponding factorial value:

 n | factorial
---|-----------
 1 | 1
 2 | 2
 3 | 6
 4 | 24
 5 | 120