Solve recursive maths problems

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

The two maths problems

Count numbers recursively
Calculate the sum of potencies

Components

Initialization step
Recursive step

Pseudo code:

Informal high-level description of the operation principle
It is intended for human reading rather than machine reading

A math blackboard

Counting numbers

Recursive definition:

number = 1 for iteration = 1,
number = number +1 for iteration > 1
termination condition: number < 4

Pseudo code:

WITH recursion AS ( 
    SELECT
          -- Initial query
       number = 1
    UNION ALL 
    SELECT
        -- Recursive query
       number = number + 1
    FROM numbers 
    -- Termination condition
    WHERE number < 4)
-- Statement on CTE 
SELECT * FROM recursion;

-- Final iteration
1+1+1+1 = 4

The sum of potencies

Recursive definition:

number = 1 for iteration = 1
number = number + iteration^iteration for iteration > 1

Pseudo code

WITH recursion AS (
      number = 1 -- Initial query
      UNION ALL
    number = number + iteration^iteration -- Recursive query
    WHERE termination_condition)
SELECT * FROM recursion;

-- Three iterations
1 + 2^2 + 3^3 = 32

Let's practice!

Hierarchical and Recursive Queries in SQL Server