What are use cases for Recursive CTEs?

Question

I am looking for examples of why you would use a recursive CTE, but the dozens of examples I have found in my web searching basically reduce to two:

• Generating a sequence
• Iterating through an employee hierarchy

I did find one example where a recursive CTE is used to split a string by commas, which is by far the most interesting (db<>fiddles: SQL Server, Postgres).

Are there examples of recursive CTEs which are not basically one of the above?

Answer 1

Here are three applications which don't involve either generating a sequence or iterating through an employee hierarchy:

1. How can I calculate all grouping permutations of an input string in SQL?
2. Sum all unique values from previous dates
3. Get top two rows per group efficiently

Paul White has a neat use for finding distinct values.

---

Iterating through an employee hierarchy

This is one example of a broader type of problem called tree problems. Recursive CTEs are great at solving tree problems in an efficient and more relational manner as opposed to iteration.

Other similar examples that exhibit parent-child relationships are BOM (Bill of Material) explosions in the manufacturing industry, actual parent-child-grandchildren relationships in ancestry problems, any other hierarchy problem, etc. Tree problems are somewhat commonplace. Recursive CTEs are very useful (in a quality over quantity of uses).

Check out explainextended.com - you can use RECURSIVE CTEs to play board games, draw stereograms and Mandelbrot sets (as well as other fractals), resolve various patterns from the game of life, write a GIF decoder... SQL is like poetry, one is only limited by one's imagination!

Answer 2

Complex Problems

You can use recursive sub queries to solve a sudoku puzzle

Oracle syntax

with x( s, ind ) as
( select sud, instr( sud, ' ' )
  from ( select '53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79' sud from dual )
  union all
  select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
       , instr( s, ' ', ind + 1 )
  from x
     , ( select to_char( rownum ) z
         from dual
         connect by rownum <= 9
       ) z
  where ind > 0
  and not exists ( select null
                   from ( select rownum lp
                          from dual
                          connect by rownum <= 9
                        )
                   where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
                   or    z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
                   or    z = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3
                                      + trunc( ( ind - 1 ) / 27 ) * 27 + lp
                                      + trunc( ( lp - 1 ) / 3 ) * 6
                                   , 1 )
                 )
)
select s
from x
where ind = 0

Other

I would imagine you could use it for any iterative process like curve fitting.

But, I find it more efficient to offload the curve fitting procrss to something like R.

Answer 3

Iterating through an employee hierarchy

At the point when you say this the question becomes basically useless.

A hierarchy is a modeled a tree. A tree is a subtype of a directed graph. All graphs can be serialized into a table, and queried through a recursive CTE. Why? Because recursion is a tool that allows you to navigate a graph. And you can't do that with SQL. You can query for a row that relates to another row statically: but you can not query for a row that relates to another row abstractly.

That it is an "employee" hierarchy is irrelevant. All graphs will require Recursive Queries when the relationship between nodes is unknown or dynamic.

For example, "threaded messages are hierarchical too". As is general ledger, a family tree, and all kinds of stuff.