Questions tagged [recursive]
Using recursion to solve database problems.
253
questions
3
votes
3
answers
350
views
Why can't unused columns and tables be ignored in a recursive CTE
Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and ...
- 82.9k
3
votes
2
answers
128
views
How to sum up the distinct Total Time of an Event ignoring duplicate overlaps in Times?
I have the following EventTimes table:
DROP TABLE IF EXISTS dbo.EventTimes;
CREATE TABLE dbo.EventTimes
(
EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY,
EventId INT NOT NULL,
StartTime TIME NOT ...
- 301
4
votes
2
answers
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 ...
- 172k
0
votes
1
answer
23
views
SQL Pattern to get "root/ start" of uneven hierarchy dataset
I feel this is a common problem and I've seen it in some SQL challenges even but for the life of me, cannot think clearly about a solution.
Say you have an uneven hierarchy. Elements that belong to ...
- 35.1k
1
vote
1
answer
2k
views
Optimizing a recursive CTE or replacing it with a temporary table
I have a recursive query like this:
with recursive PCte(id) as
(
select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in
('123', '124','125', 'n')
union all
...
CommunityBot
- 1
1
vote
1
answer
1k
views
How to select recursively in a child parent design situation (in MySQL)?
Let's take into consideration the following tables:
CREATE TABLE actions
(
id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
user_id BIGINT(20) ...
CommunityBot
- 1
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&...
- 172k
1
vote
0
answers
15
views
Find best intersect (most elements) between Order (haystack) and Pricerules (needle)
Full disclosure
I have posted a greater scoped question at SO a couple of days ago with a bounty (no answers yet).
Scope here
How can a CTE be used to find a match with the most elements?
For the ...
- 209
2
votes
1
answer
3k
views
Redshift- How to use previous row's calculations in current row
I am using redshift to determine projected inventory for next few week. I have receipts, current inventory and forecast.
ei= ei(previous row) + receipts - forecast
wk bi r f ei
1 100 20 ...
CommunityBot
- 1
1
vote
1
answer
35
views
PostgreSQL recursive query performance improvement
I'm building a database which is used to derive hierarchical relations for which I chose a PostgreSQL recursive CTE .
my table structure is
table_name |column_name |data_type ...
- 47.9k
3
votes
2
answers
455
views
From last id, recursively find parent id and fill column
CREATE TABLE t1
(
COL1 nvarchar(36),
COL2 nvarchar(36),
ExpectedResult nvarchar(36)
)
INSERT INTO t1 (COL1, COL2, ExpectedResult)
VALUES
('CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-...
- 33
0
votes
1
answer
2k
views
While Loop - Parent/Child Tree
I'm trying to recursively loop through and return all child_id's that have the root element of 9.
The structure:
->9
->->8
->->->17
->->22
->->->11
Parent Child ...
CommunityBot
- 1
0
votes
1
answer
350
views
How to use a recursive CTE to get ancestors in a hierarchy
create table division (
id serial primary key,
name varchar not null
);
-- nested sets table
create table location (
id serial primary key,
name varchar not null,
division_id integer not ...
CommunityBot
- 1
2
votes
1
answer
13k
views
Query to List all hierarchical children with parent, grandparent etc. from entire table
I have a basic sql table with an ID and an optional parentID from the same table. This has multiple levels. I need to get a list of all IDs with each of its parents all the way to the top.
if ...
CommunityBot
- 1
0
votes
1
answer
508
views
write SQL query that takes its own output as input
In a table that displays how trades are related to each other, starting from one trade, I want to find all other trades that relate to the first trade:
713613 PostPTETradeIDs 718478
713613 ...
0
votes
0
answers
21
views
Finding tree contours using nested set model
Is there a query or algorithm that will enable me to find the left and right contours for general hierarchical trees and subtrees described using the nested sets model?
I have described tree ...
- 131
-1
votes
1
answer
57
views
My recursive query runs with no end in sight. Any idea as to how to solve this?
With A as(
select client_id, convert(date,admission_date) start_date,
year(convert(date,admission_date)) start_year,
month(convert(date, admission_date)) start_month
from ...
- 8,611
0
votes
1
answer
454
views
MySQL Select query for a calculated column that uses previous row value for current row
I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated.
Table has 3 columns - Employee, Month, Salary. ...
CommunityBot
- 1
-2
votes
1
answer
86
views
I want to make a tree of parents only in sql server
My table in the database
How can I get my result as Where child = 'chl1'?
- 1,480
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 ...
- 172k
0
votes
1
answer
136
views
How can I recursively query a table containing LTREE to get the tree like structure when some of the parent paths are missing?
Lets say I have this table:
CREATE TABLE nodes (node_path ltree);
INSERT INTO nodes VALUES ('Top.Science');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO nodes VALUES ('...
- 47.9k
1
vote
0
answers
138
views
Is it possible to limit infinte recursion in an Oracle CTE?
In a DBMS like Microsoft SQL, there is a default limit of 100 recursions, in a recursive CTE, which can be adjusted with:
WITH cte AS (
…
)
SELECT * FROM cte OPTION (MAXRECURSION …);
As far as I ...
- 2,759
0
votes
1
answer
113
views
"ON UPDATE" in self referencing table
I have a table Category with a parent_id column referencing the self id column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too.
At ...
- 69
1
vote
0
answers
40
views
Mysql8 Unable to order the result of WITH RECURSIVE query
I am using the WITH RECURSIVE query to generate a hierarchy of users. The data and the query used is shared in https://www.db-fiddle.com/f/oTbCcMQb5a4B6V96DAYJHG/0 . The query is returning the results ...
- 111
8
votes
2
answers
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
0
votes
0
answers
946
views
How to fix error "Line 25: The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
I added OPTION (MAXRECURSION 1000) at the very bottom but it was not enough. My query:
Use DatabaseName;
DECLARE @SOURCE_TABLE_NAME NVARCHAR(200) = 'WorkOrderVersion'
DECLARE @SOURCE_SCHEMA_NAME ...
- 1
1
vote
0
answers
149
views
How to write query to link complex Parent-Child relationships
We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a ...
- 11.8k
8
votes
1
answer
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 ...
- 82.9k
3
votes
1
answer
241
views
Return recursive self joins in PostgreSQL with aggregated data
I have this simple table schema:
SQL Fiddle
PostgreSQL 9.6 Schema Setup:
CREATE TABLE IF NOT EXISTS users(
id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL, ...
- 65
4
votes
2
answers
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 |
+----+-----------+
|...
0
votes
1
answer
112
views
Why PostgreSQL RECURSIVE query iteration does not return the same result as manually created one?
I have the following test table:
SELECT * FROM attempts order by id;
id | user_id | name
----+---------+------
1 | 1 | a
2 | 2 | b
3 | 1 | b
4 | 1 | c
5 | 2 | ...
- 1,413
1
vote
2
answers
330
views
How does PostgreSQL recursive query treat SELECT MIN(ID) FROM T on each iteration?
I have the following query that I do not understand how it works, especially the subquery that returns the minimum ID without grouping.
WITH RECURSIVE t(id, user_id, name) AS (
select min(id) - 1, ...
- 121
0
votes
0
answers
169
views
Make this recursive CTE more dynamic
I have this script that enables me to extract hierarchies and the description for each level however the number of levels has to be known beforehand to define the number of select queries necessary ...
- 82.9k
1
vote
0
answers
46
views
Recursive CTE get
I have the following tables:
Table1
id
entity_id
value
Table2 - Same structure as Table1
id
entity_id
value
Table3
source_id
destination_id
entity_id
Table1 and Table2 are both partitions from ...
- 11
0
votes
1
answer
169
views
How does SQL Server know not to execute Anchor element in Recursion CTE for 2nd Iteration / 1st Recursion?
I've been reading up on the inner workings of CTE Recursion and I'm still not quite sure how SQL Server is implementing the Anchor element in their source code and how SQL Server Query Execution ...
- 275
0
votes
1
answer
105
views
Optimizing query that finds closest unique records
I've been trying out different queries to get the best performance when querying hierarchical data on Postgres 13.6.
Fiddle: https://dbfiddle.uk/060f_h96
Explain: https://explain.dalibo.com/plan/...
0
votes
0
answers
75
views
Hierarchical query to select `children` from `children`
I have the following table mytable where id is a unique identifier at a given r (resolution) and children is an array of unique id's of r + 1.
id
r
children[]
0
2
{0,2467972595799031808,...
- 275
0
votes
0
answers
133
views
Optimising a recursive SQL query that processes several million records in BQ
I need help optimizing a recursive SQL query in BQ.
I have hierarchical data stored in a table as parent-child relationships, i.e
will be stored as
parent_item_id
child_item_id
1
1
1
2
1
3
3
4
...
- 101
0
votes
0
answers
131
views
Update child tables when an id field updated In main table
I have this sp to delete child from child tables when something deleted from the main table .
Now I want the same function with update statement . When I update the id field on main table . I want to ...
- 89
0
votes
0
answers
139
views
How to use variable in the select query PostgreSQL
I'm looking for a way to write a recursive query, but I'm getting an error.
"SQL Error [42601]: ERROR: syntax error at or near "with recursive"
Position: 287"
query result "...
- 1
1
vote
1
answer
546
views
How to find child and grandchildren tables of a main table by order
I have this script to return all child and grandchildren tables with their level from a database . How do I change this if I only want to return the child and grandchildren tables of a particular ...
- 35.1k
3
votes
1
answer
184
views
String Manipulation of the Result from Recursive CTE
Good afternoon everyone
I found just one post here within the last year about this, but it doesn't help my situation.
I have been working with MySQL and trying to improve my knowledge of recursive CTE....
- 32k
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 ...
- 1,480
3
votes
1
answer
49
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 ...
- 49
0
votes
1
answer
132
views
Generate hierarchical structure in sql server
I have a table employees
id
name
manager
1
susan
null
2
john
1
3
mary
1
4
josh
3
5
mathew
4
6
leo
2
7
peter
5
I want an output like:
id
name
hierarchy
1
susan
null
2
john
susan->john
3
...
- 930
1
vote
1
answer
829
views
Generate dates using recursive cte
I have a table sales
product_id period_start period_end sale
----------------------------------------
1 2019-01-25 2019-02-28 100
2 2018-12-01 2020-01-01 10
3 ...
- 4,391
1
vote
1
answer
597
views
Why does a Postgres recursive CTE need the list of columns as argument?
This is kind of a subjective question.
If I have a recursive CTE something like:
WITH RECURSIVE r(x, y) AS ( ...
SELECT x, y -- select1
FROM t WHERE ..
UNION
SELECT t.x, t.y -- ...
- 97.4k
2
votes
0
answers
115
views
Problem with sysdepends while checking dependencies
I have this script to list all dependencies of all views in a database . I get the dependencies in the right order when I use sysdepends ,but I am missing a few dependencies while using sysdepends. ...
- 2,146
0
votes
1
answer
39
views
Retreive upper heirarchy
I have Categories stored in a single table.
id name parentId path
A1 Cat 1 0 Cat 1
A2 Cat 2 A1 Cat 1 > Cat 2
A3 Cat 3 A2 Cat 1 > Cat 2 > Cat 3
...
- 2,146
0
votes
1
answer
549
views
How can I handle cycles / infinite loops in recursive CTEs in MySQL
Cycles in graphs lead to infinite loops in CTEs.
Dealing with them in Postgres is straightforward.
Since 8.0 MySQL also allows for CTEs. How I can detect cycles and infinite loops in MySQL CTEs?
The ...
- 19.4k