30 votes

CTE: Get all parents and all children in one statement

If you want all ancestors and all descendants, you can combine the two queries in one. Use the two CTEs and then a simple UNION: WITH RECURSIVE -- descendants rec_d (id, name) AS ( ...
ypercubeᵀᴹ's user avatar
28 votes
Accepted

How does SQL recursion actually work?

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows: Split the CTE expression into anchor and recursive members. Run the anchor member(s) creating ...
Martin Smith's user avatar
  • 84.7k
17 votes
Accepted

Recursive CTE to find Total for all children

This recursive CTE (SQL Fiddle) should work with your sample: WITH cte(ParentID) AS( SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet' UNION ALL SELECT i.ParentID FROM cte c ...
Julien Vavasseur's user avatar
13 votes
Accepted

Simplifying Function with Recursive CTE and/or Window Function

You need an extra column to carry along the running total (fiddle). In the recursive part of the CTE below R refers to the "previous" row and A the current one so referencing the column from R is ...
Martin Smith's user avatar
  • 84.7k
12 votes

self-joins in recursive CTE: `recursive reference must not appear more than once`?

Indeed error like recursive reference to query "x" must not appear more than once is some strange restriction applied in postgres. And I made assumption it is because their parser just simple ...
Hubbitus's user avatar
  • 221
12 votes
Accepted

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

Randi Vertongen's answer correctly addresses how you can get the plan you want with the parameterized version of the query. This answer supplements that by addressing the title of the question in case ...
Paul White's user avatar
  • 84.4k
10 votes

Change the system wide default for maxrecursion

If your queries have a common shape, you might be able to add the required maxrecursion hint using one or more plan guides. There can be a knack to getting them right. If you add specific query ...
Paul White's user avatar
  • 84.4k
10 votes

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

Though at the moment I don't have the title of the actual hotfix, the better query plan will be used when enabling the query optimizer hotfixes on your version (SQL Server 2012). Some other methods ...
Randi Vertongen's user avatar
9 votes

Change the system wide default for maxrecursion

If you have absolutely have to use a function (a limitation of your ETL tool as you imply), you can specify OPTION as part of a multi-statement table-valued function, eg something like this: CREATE ...
wBob's user avatar
  • 10.4k
9 votes
Accepted

PostgreSQL - retrieve all IDs in a tree for a given subnode

A very primitive implementation: It basically divides the problem into two subproblems: First find all the ancestors of the node in question (including the node itself). If the node has no parents, ...
ypercubeᵀᴹ's user avatar
9 votes
Accepted

How can I find the shortest path between two currencies?

You could use this recursive query: WITH RECURSIVE c AS ( SELECT c1, c2 FROM currency_pair UNION SELECT c2, c1 FROM currency_pair ), curr_path AS ( SELECT ARRAY[c1, c2] AS path ...
Laurenz Albe's user avatar
  • 51.8k
9 votes

How can I calculate all grouping permutations of an input string in SQL?

I guess that is just a challenge for fun, but here is my solution: WITH s(s) AS (VALUES ('ABCD')) SELECT substr(s, 1, 1) || string_agg( CASE WHEN i & (2::numeric ^ p)::bigint = 0 ...
Laurenz Albe's user avatar
  • 51.8k
7 votes

Select all children down a hierarchy

The technique to use is called a recursive common table expression, or recursive CTE. The Microsoft SQL documentation is comprehensive and there are a lot of third-party blog posts to help explain. ...
Liya Tansky's user avatar
7 votes

PostgreSQL: Calculate balance working backwards from current balance

You need a Window Function indeed. However LAG is not the right one. SUM(...) OVER(...) is the one you want. See SQL Fiddle. Query: SELECT account_id, activity_date, amount , SUM(amount) OVER(...
Julien Vavasseur's user avatar
7 votes

How does SQL recursion actually work?

This is just a (semi) educated guess, and is probably completely wrong. Interesting question, by the way. T-SQL is a declarative language; perhaps a recursive CTE is translated into a cursor-style ...
Hannah Vernon's user avatar
  • 70.1k
7 votes

Grouping by overlapping arrays, transitively, without duplicates

One of the key parts of Jack Douglas's solution in Group by array overlapping is the | (pipe) operator used on arrays in the recursive part of the recursive t CTE like this: ... select t.id, a.id, t....
Andriy M's user avatar
  • 23k
7 votes
Accepted

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Thanks to the helpful guys at the pgbugs mailing list, it turned out that Just-in-time-compilation (some helpful background information to be found here) being turned on by default in PostgreSQL 12 ...
cis's user avatar
  • 459
7 votes
Accepted

String manipulation in Recursive CTE

You can cast it to varchar(2000) or varchar(max) depending on your needs so both should have the same data type and size for value 1, it using an integer/int data type From BOL related to CTE , The ...
Sabin B's user avatar
  • 4,401
7 votes
Accepted

Schema-binding with recursive scalar UDF

Self-referencing functions are not well documented in general, but the restriction on schema-binding such functions has been around forever. Not every error condition is mentioned in the parent syntax ...
Paul White's user avatar
  • 84.4k
6 votes
Accepted

How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

There are a few options that I was able to get working. All of the options deal with variations of filter predicates. NOTE: you must disable the Server Audit in order to make changes, and then re-...
Solomon Rutzky's user avatar
6 votes
Accepted

Why does this CTE return the bulk of its results immediately, but then take several minutes to finish?

The anchor part of the recursive SQL query produces all 7488 rows. On my machine, that part of the query finishes in under 100 ms. SSMS does not immediately show all 7488 rows in the grid results. It ...
Joe Obbish's user avatar
  • 32.2k
6 votes
Accepted

CTE query that fails to find ancestors

I believe you made a mistake in your join predicate. Try: WITH chainIDsUpwards AS ( SELECT id, parent_id FROM foo WHERE id = @starting_id UNION ALL SELECT foo.id, foo....
Lennart - Slava Ukraini's user avatar
6 votes
Accepted

Determine Nodes in Network with PostgreSQL

Approach 1: At first sight, it seems that I could apply a basic solution because, according to your sample data, each single connection is included in another connection. SELECT e1.node, e1....
McNets's user avatar
  • 23.8k
6 votes
Accepted

Get the highest parent id recursively in MySQL

WITH RECURSIVE cte as ( SELECT id, id nextid, parentid FROM t1 UNION ALL SELECT cte.id, t1.id, t1.parentid FROM t1 JOIN cte ON cte.parentid = t1.id ) ...
Akina's user avatar
  • 19.9k
6 votes
Accepted

How to turn a set of flat trees into a single tree with multiple leaves?

A recursive CTE (rCTE) is the right tool to follow the path and identify all nodes of a tree. But it does not allow aggregation in the recursive term. There is an elegant alternative: Recursive ...
Erwin Brandstetter's user avatar
5 votes

PostgreSQL - retrieve all IDs in a tree for a given subnode

This function returns the parent level of node_id: There is a 'level' row due there isn't a row (id, null) for parent row. CREATE FUNCTION get_parent(node_id int) RETURNS integer AS $$ WITH ...
McNets's user avatar
  • 23.8k
5 votes

How does SQL recursion actually work?

The SQL Server documentation, which mentions Ti and Ti+1, is neither very understandable, nor an accurate description of the actual implementation. The basic idea is that the recursive part of the ...
CL.'s user avatar
  • 5,213
5 votes
Accepted

Exclusion constraint on recursive foreign key (on the example of a Tree)

Use a composite foreign key: (tree_id, parent_id) REFERENCES (tree_id, id) You will need to first change the PK or add a UNIQUE constraint on (tree_id, id)
ypercubeᵀᴹ's user avatar
5 votes

Recursivly get a Tree Via self joined table

Recursive CTEs are notoriously hard to learn. Take a look at this fiddle to play with a working model. The 2nd query in the CTE, or Common Table Expression, needs to reference the first query. This ...
Hannah Vernon's user avatar
  • 70.1k

Only top scored, non community-wiki answers of a minimum length are eligible