Questions tagged [recursive]
Using recursion to solve database problems.
262 questions
33
votes
2
answers
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,179
22
votes
4
answers
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:
...
- 409
20
votes
5
answers
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
20
votes
1
answer
38k
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,561
19
votes
1
answer
14k
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 ...
- 525
17
votes
2
answers
15k
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
15
votes
2
answers
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
14
votes
4
answers
73k
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 |
+----+-----------+
...
- 261
12
votes
3
answers
5k
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 ...
- 229
12
votes
1
answer
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 ...
- 783
11
votes
2
answers
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
10
votes
2
answers
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,692
10
votes
1
answer
3k
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
10
votes
2
answers
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
...
- 906
9
votes
2
answers
3k
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 ...
- 463
9
votes
2
answers
7k
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 ...
- 123
9
votes
2
answers
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,274
8
votes
1
answer
623
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 ...
- 14.4k
8
votes
2
answers
679
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 ...
- 439
8
votes
1
answer
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
7
votes
1
answer
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 ...
- 479
7
votes
1
answer
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,119
7
votes
2
answers
6k
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
7
votes
1
answer
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
7
votes
2
answers
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
6
votes
3
answers
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&...
- 64.6k
6
votes
1
answer
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
6
votes
1
answer
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 ...
- 525
6
votes
1
answer
666
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
6
votes
2
answers
4k
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 ...
5
votes
1
answer
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:
...
5
votes
2
answers
281
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
(...
- 664
5
votes
1
answer
683
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,...
- 587
5
votes
1
answer
2k
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
5
votes
1
answer
300
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 ...
- 53
5
votes
1
answer
324
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
5
votes
2
answers
8k
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 ...
- 179
5
votes
2
answers
263
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
4
votes
2
answers
2k
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
4
votes
2
answers
8k
views
Get the highest parent id recursively in MySQL
I want to find the uppermost parent ID for each entry. For example,
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
- 4,531
4
votes
2
answers
442
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 | ...
4
votes
1
answer
997
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,130
4
votes
1
answer
2k
views
How to optimize a recursive query in Postgresql?
I have a recursive query that takes too long - 30+ ms where doing the individual queries to extract the same data manually takes < 0.12 ms. So we're talking 250x as long.
I have the following ...
- 187
4
votes
1
answer
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
4
votes
1
answer
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
4
votes
1
answer
2k
views
How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)
I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
- 543
4
votes
3
answers
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
4
votes
1
answer
77
views
best practice for dependency roots
When a database has a dependency table showing the parent-child relationship of items...
I've mostly seen guides that assume the top level parent has a "parent id" field which is either zero ...
- 41
4
votes
1
answer
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,463
4
votes
2
answers
1k
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