Questions tagged [recursive]
Using recursion to solve database problems.
249
questions
0
votes
0
answers
14
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
55
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 ...
- 21
-2
votes
1
answer
54
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'?
0
votes
1
answer
54
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 ('...
1
vote
0
answers
77
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,719
0
votes
1
answer
79
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 ...
- 23
1
vote
0
answers
31
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
199
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
485
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
136
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
8
votes
1
answer
431
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.5k
3
votes
2
answers
285
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
3
votes
1
answer
194
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
0
votes
1
answer
352
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. ...
- 1
0
votes
1
answer
100
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 | ...
- 121
1
vote
2
answers
280
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
142
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 ...
- 1
1
vote
0
answers
45
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
133
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
0
answers
71
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,...
- 273
0
votes
0
answers
107
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
67
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
126
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
460
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 ...
- 57
3
votes
1
answer
156
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....
- 33
0
votes
1
answer
95
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
...
- 171
1
vote
1
answer
639
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 ...
- 171
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 ...
- 31
1
vote
1
answer
507
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 -- ...
- 759
2
votes
0
answers
103
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. ...
- 89
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
...
- 103
0
votes
1
answer
402
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 ...
- 759
0
votes
1
answer
158
views
Retrieve all linked categories
I have Categories stored in a single table.
Where there is no limit on number of childerns.
I want to fetch all the linked categories for the provided category id:
The reason for getting the hierarchy ...
- 103
1
vote
0
answers
56
views
TSQL - retrieve all IDs in a tree for a given subnode
I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode, but I'm either programming it wrong, or ...
- 111
2
votes
1
answer
1k
views
Concatenate all previous row values from a TEXT column in reverse order
My Postgres version is 11.15. I have a table like:
id
employee
1
Anna
2
Bill
3
Carol
4
Dave
5
Tom
The output I need is
id
cumulative_employees
1
Anna
2
Bill, Anna
3
Carol, Bill, Anna
4
...
0
votes
0
answers
235
views
How to optimize (or get an alternative faster solution to) this recursive query that groups using recursive CTEs in Postgres?
I am on the quest to implement an NMS filter for labels in a Database.
For context, here's a link to my first question, where I explain what NMS is and what I'm trying to achieve (I will edit that ...
- 51
1
vote
3
answers
138
views
Finding the Second (time) Interval within X of the current time
I have a need to find, for a given current second and upper and lower boundary, find all of the seconds that would fall within that, wrapping at the 59 second mark.
With that word salad out of the way,...
- 8,571
0
votes
0
answers
162
views
Mysql parent child to pivot table
I want to convert my parent child table to pivot,below is my query
CREATE TABLE parent_child
with recursive cte (id, name, parentid,level) as (
select id,
name,
...
0
votes
1
answer
98
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/...
1
vote
0
answers
105
views
How to keep up-to-date sums in the tree?
I have a tree table. I need to ensure that when inserting or updating a record, the sumvalue field of all parents is updated - this is the sum of all child elements. I wrote a trigger (even a query is ...
- 119
2
votes
2
answers
235
views
0 and 1 FROM DUAL Matrix
I need to make a matrix with 0 and 1 combination :
SELECT 1 AS F1, 0 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 1 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 ...
- 29
3
votes
1
answer
708
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 ...
- 177
2
votes
1
answer
405
views
Stopping recursive query on first match in Postgres 11
Im struggling a bit with recursive queries in Postgres.
Given these tables:
create table resource_v3
(
id uuid not null primary key,
name varchar(255),
resource_id ...
- 127
0
votes
1
answer
162
views
Calculate the number of days that the field value has not changed
Please consider the following table:
User
Date
amount
John
1/1/2022
40
John
1/2/2022
-30
John
1/6/2022
50
...
...
...
Jack
4/1/2022
80
Jack
4/1/2022
-60
We have three records for John and two ...
- 115
0
votes
1
answer
825
views
Filtering a recursive CTE as view
i'm using MSSQL Server 2017 and have a CTE used in a view to get the root element of a hirarchical tree linked to one specific element.
If the CTE is used directly and the base elements are allready ...
- 3
0
votes
1
answer
134
views
Recursive querry over 3 Tables
I have the following table structure
create table folder
(
id integer
constraint folder_pk
primary key,
path TEXT
);
create index folder_path_uindex
on folder (path);...
1
vote
0
answers
45
views
Splitting time period into multiple with irregular length, grouped by name
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 123
2
votes
1
answer
571
views
postgresql Recursive Update resulting in Sequential Scan
I have a table with a self-referential foreign key, and I would like to update a given parent, and all of the descendants of the parent, with the same recursive update.
When using an UPDATE with a ...
- 187
0
votes
0
answers
1k
views
SQL (postgres) - Recursive view for all parents of a child
Solved: Solution from crossposting: https://stackoverflow.com/a/71149082/8082262
I have two tables: community2collection(collection_id,community_id) and community2community(child_comm_id,...
0
votes
1
answer
173
views
Can EXPLAIN be used to get some insights about Common Table Expression (CTE)?
I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL ...
- 1,710