# Questions tagged [recursive]

Using recursion to solve database problems.

253 questions
Filter by
Sorted by
Tagged with
12k views

### Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
• 2,159
5k views

### How does SQL recursion actually work?

Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart. Consider the following simple example: ...
• 399
9k views

### How to recursively find gaps where 90 days passed, between rows

This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this. SELECT ...
• 213
13k views

### How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
• 515
35k views

### CTE: Get all parents and all children in one statement

I have this working CTE example. I can select all grand-parents and all children. But how can I select all grand-parents and all children in one statement? In this example I want Grandfather, Father, ...
• 1,481
14k views

### Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
• 314
6k views

### PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
• 151
70k views

### Getting all descendants of a parent

I have a table with two columns, Parent and Child. I need to get the list of all descendants associated with the parent records. Source Table +----+-----------+ | Parent | Child | +----+-----------+ ...
• 251
4k views

### Change the system wide default for maxrecursion

How do I change the system-wide default value of MAXRECURSION? By default it is 100, but I need to increase it to something like 1000. I am unable to use query hints since I'm using a program that ...
1k views

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

We have a SQL Server database which has a database audit specification which audits all execute actions on the database. CREATE DATABASE AUDIT SPECIFICATION [dbAudit] FOR SERVER AUDIT [servAudit] ADD ...
3k views

### Conceptual ERD Multi-table many to many, or possibly recursive?

I'm creating a conceptual diagram [yes, I know I've included attributes and keys - but this is just for me to consolidate what I'm doing whilst learning] -- so please treat it as Conceptual with the ...
• 213
2k views

### Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function. After days, I've boiled the function down to (pseudocode) where I have N and B, and need to generate E: En =...
• 232
2k views

### Finding all joins required to programmatically join a table

Given a SourceTable and a TargetTable, I would like to programmatically create a string with all joins required. In short, I am trying to find a way to create a string like this: FROM SourceTable t ...
• 890
2k views

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

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
• 452
3k views

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

This is tested with PostgreSQL 9.6, but it's a general SQL question. Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)? I tried the following rCTE containing a self-...
• 3,425
6k views

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

I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node. The table is very simple, just an join table with a parent id and child id. This is a ...
• 113
1k views

### How can I find the shortest path between two currencies?

I have table currency_pair(c1, c2); values are (usd,bnb), (cake,bnb), (cake,eth). I need to find the shortest path that allows me to compare usd to eth. The result here would be those same values. I ...
• 1,145
463 views

### Schema-binding with recursive scalar UDF

TL;DR; Is it a bug that SQL Server allows a scalar UDF to recursively call itself when schema-bound, but only when altered to do so using the CREATE OR ALTER syntax? Or is it a bug that other syntaxes ...
• 11.8k
278 views

### How can I efficiently traverse graph data with this pattern?

I have some relations embodying a directed acyclic graph that includes patterns similar to the following: I'm looking for an efficient way to traverse this graph data. Here is an example of the ...
• 421
6k views

### Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
• 101
2k views

### Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
• 1,089
5k views

### Query to find circular references

I have two tables ID Task 1 1 2 2 3 3 4 4 Col1 depend 2 3 2 4 3 1 4 2 ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
• 111
1k views

### Recursivly get a Tree Via self joined table

Using other questions here and Postgresql documentation I've managed to build a many-to-many self joined table. However adding a WHERE clause is giving me trouble. Problem: A Category can have many ...
• 171
4k views

### Looking for a simpler alternative to a recursive query

The actual query is more involved, but the problem I'm facing can be distilled to this: A query to filter a rowset of monotonically increasing integers so that - in the final result set, row(n+1)....
• 71
1k views

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

Given an input like "ABC" generate a query that calculates all potential splits of 0 or more of the given string, Desired output, A B C A BC AB C ABC Given an input like "ABCD&...
• 62.1k
2k views

### String manipulation in Recursive CTE

I am trying to make something like the following work: WITH results(n, string ) AS ( SELECT 1,'lol' UNION ALL SELECT n+1, string + ' lol' FROM results ...
• 249
4k views

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

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
• 389
2k views

### Help with CTE Aggregating Children Recursively

I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised ...
• 403
542 views

### Tree Node Calculation - SQL Server

I'd appreciate some help with something I am trying to achieve please. The parts that my company make are built from sub assemblies and sub assemblies of sub assemblies. This looks like: ...
• 63
3k views

### Improve performance for order by with columns from many tables

Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
3k views

### Count the nodes in a binary tree structure

I need to count the left and right nodes in a binary tree structure (output grouped by joiningDate), given a particular starting node parent id (pid). The tree is stored in the table shown below: ...
267 views

### CTE query that fails to find ancestors

I have a table foo with the content So the records of the table foo can graphically be represented with this graph. When I run a procedure that should return ancestors using @starting_id=9 (...
448 views

### Grouping by overlapping arrays, transitively, without duplicates

I've found: Group by array overlapping https://stackoverflow.com/a/23650080/5419599 However, I'm having trouble putting it to use in my case. I have a table like so (the real myid values are hashes,...
• 588
1k views

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

We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once: item_id jsonb_pretty 1 { "title": "PARENT", "...
• 75
288 views

### Determine Nodes in Network with PostgreSQL

I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes ...
299 views

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

I've got a schema roughly like this (this is a simplification of my actual schema): CREATE TABLE foo ( key1 NUMERIC(6) NOT NULL, key2 VARCHAR(32) NOT NULL, val VARCHAR(255) NULL, ...
• 163
244 views

### How to group all linked objects in a undirected cyclic graph

I have a table that has customers and its joint customers. e.g Customer 1 has a joint customer 2. Customer 1 is also a joint customer to Customer 3. I am trying to group all customers that are linked ...
• 213
1k views

### Generate all combinations of a list of strings

I'm trying to generate all combinations of a list of Strings list = ['A', 'B', 'C', 'D']. I want to generate all possibilities and then search in the DB ABCD, ABC, ABD, ACD, BCD, AB, AC, AD, BC, BD, ...
• 43
402 views

### Finding earliest connected value over two columns

I have a, perhaps slightly esoteric, use case. Given a table like the following: id1 | id2 | timestamp -----+-----+--------------------- 3 | 4 | 2016-03-22 09:52:15 1 | 2 | ...
950 views

### Why does Recursive CTE estimate just 1 row?

Given two cascading, self-contained (no real tables) recursive CTE's: create view NumberSequence_0_100_View as with NumberSequence as ( select 0 as Number union all select Number + 1 ...
• 2,066
2k views

### Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
• 143
5k views

### Making a recursive CTE like query, but for many records

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code : declare @Id integer ;WITH CTE AS ( SELECT ...
• 43
7k views

### Create JSON object from recursive tree structure

Having this simple many-to-many self-referential structure. An item owns other items through the joins table: CREATE TABLE items ( item_id serial PRIMARY KEY , title text ); CREATE TABLE ...
• 169
2k views

### Best way to design / query multi-level relationship table

My company is going to develop a mobile app selling services across different regions. We are expecting users are going to hugely depend on the Search function. Thus, I need to make sure the database ...
• 41
11k views

### Recursive cte avoiding loops

I'm trying to write a recursive CTE to explore a workflow system. Unfortunately, I'm getting maximum recursion errors due to loops: with cteActs as ( select a.id as [id], aa.TASKNEXTID as [...
• 1,453
937 views

### Informix - Getting all descendants of a parent

I have a table with two columns, Parent and Child. Need to get the list of all descendants associated with the parent records. Source Table: +----+-----------+ | Parent | Child | +----+-----------+ |...
• 43
2k views

### Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion

Before posting, I have looked for a good solution on internet and I have coined this post for 3 days in row and trying to think through it prior this post. So if I have missed something or got it ...
• 143
4k views

### Table design for “Users & Groups” plus “Groups within Groups”

I need to represent users and groups, where groups can be nested within other groups. (Not my design choice – I am replicating another system and must mimic this behavior. I have no access to its ...
• 10.6k