sql WITH recurtion - ghdrako/doc_snipets GitHub Wiki

• https://github.com/ghdrako/doc_snipets/wiki/postgres-with-recurtion-graph

A recursive query is made up of two parts: the base case and the recursive case. The base case is where we want to start our query. The recursive case is the “loop” that will continue to run until some endpoint is reached.

WITH RECURSIVE {name} AS (
  {base case}
  UNION
  {recursive case}
)

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT * FROM cte_name

Example Oracle

arg2 jest rodzicem dziecka arg1

w pierwszym zapytaniu wybieramy najwyzej w hierarchi ktory albo nie ma rodzica albo rodzic nie istnieje jako dziecko w tabeli

w drugim zapytaniu wybieramy takie rekordy dla ktorych przodkiem jest rekord/y z pierwszego zapytania

WITH    Ancestor(arg1, arg2) AS
        (
        SELECT  p.arg1, p.arg2
        FROM    parent p
        WHERE   arg2 NOT IN
        (
            SELECT  arg1
            FROM    parent
        )
        UNION ALL
        SELECT  p.arg1, a.arg2
        FROM    Ancestor a 
        JOIN    parent p
        ON      p.arg2 = a.arg1
        )
SELECT  *
FROM    Ancestor

Oracle only supports recursive CTE since 11g Release 2.

In earlier versions, use CONNECT BY clause:

SELECT  arg1, CONNECT_BY_ROOT arg2
FROM    parent
START WITH
        arg2 NOT IN
        (
        SELECT  arg1
        FROM    parent
        )
CONNECT BY
        arg2 = PRIOR arg1