Questions tagged [recursive]
Using recursion to solve database problems.
262 questions
0
votes
0
answers
173
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
1k
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 ...
- 39.5k
3
votes
1
answer
318
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....
- 32.4k
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,762
4
votes
1
answer
78
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
388
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
2k
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,486
1
vote
1
answer
1k
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 -- ...
- 98.6k
2
votes
0
answers
180
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,482
0
votes
1
answer
43
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,482
0
votes
1
answer
1k
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 ...
- 20.3k
0
votes
1
answer
278
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 ...
- 2,482
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 ...
- 98.6k
1
vote
0
answers
62
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
0
votes
0
answers
385
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
3
votes
1
answer
2k
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
...
- 14.4k
1
vote
3
answers
226
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,...
- 318
0
votes
0
answers
214
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,
...
1
vote
0
answers
121
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
254
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 ...
- 98.6k
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 ...
- 182k
2
votes
1
answer
1k
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 ...
- 98.6k
0
votes
1
answer
177
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 ...
- 30.6k
0
votes
1
answer
1k
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 ...
- 14.4k
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 =...
- 90.2k
0
votes
1
answer
187
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);...
0
votes
1
answer
146
views
calculate or get price of parent items on bill of materials
i want to calculate or get the price of parent items on bill of materials query
here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6
please note in the second row, betax has ...
CommunityBot
- 1
1
vote
0
answers
76
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
961
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 ...
- 207
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,...
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 ...
- 182k
2
votes
1
answer
765
views
Recursive CTE in PostgreSQL to generate data with different frequencies
I am trying to write a data generation query which uses recursive CTE in Postgresql 14.
Consider schema "sc" which includes a function getfreq. getfreq takes an int as param (which ...
- 187
3
votes
3
answers
2k
views
What are use cases for Recursive CTEs?
I am looking for examples of why you would use a recursive CTE, but the dozens of examples I have found in my web searching basically reduce to two:
Generating a sequence
Iterating through an ...
- 64.6k
3
votes
2
answers
5k
views
Recursive query on a self-referential table where each node has one link to its child's node
Consider the following table and data (fiddle available here):
CREATE TABLE test
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
next_id INTEGER NOT NULL
);
INSERT INTO test (id, next_id) ...
- 30.6k
1
vote
1
answer
2k
views
Social network comments and posts model in PostgreSQL 9.6
I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post.
The thing is that each ...
- 64.6k
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, ...
0
votes
2
answers
2k
views
Update rows based on a recursive CTE query result
This query shows all lines recursively based on a uuid / parentUUID relationship:
WITH RECURSIVE files_paths (id, parent) AS
(
SELECT uuid, parentuuid
FROM core_data
WHERE uuid = '2c828bbb-...
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, ...
0
votes
1
answer
79
views
How can I include an entry for an intermediate row In a recursive query summing up children
I have a schema with activity(activity_id, parent_activity_id, name) and period(activity_id, duration, etc).
An activity can have ZERO to many associated periods. Further, activities have a reference ...
- 121
3
votes
2
answers
5k
views
SQL Server CTE Bottom to Top Recursive with Where clause
I have an Employee Table with an EmployeeId, ManagerId and a Name field.
The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).
I found ...
2
votes
1
answer
782
views
Why my not-null columns become nullable in my CTE recursive query?
I have a simple hierarchical table. Let's say it has these columns:
Id, [Guid], ParentId, Title
Only ParentId is nullable. Other columns are not nullable.
I have created a recursive query to show me ...
- 14.4k
0
votes
5
answers
4k
views
Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?
Example you have the following table
CREATE TABLE emp (id,name,salary,manager)
AS
VALUES
( 1 , 'james' , 10000 , null ),
( 2 , 'alex' , 5000 , 1 ),
( 3 , 'Alice' , 4500 , 1 ),
...
3
votes
2
answers
560
views
Optimization of recursive view
I have an optimization problem when using a recursive view on PostgreSQL. When I perform a trivial query using this view, the execution time is abnormally long.
To expose my problem, here is a ...
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",
"...
- 182k
0
votes
1
answer
2k
views
How to eliminate duplicates made by recursive CTE [closed]
I have following schema in PostgreSQL 12 (objects and their relations are versioned by date ranges):
CREATE TABLE tmp_deps (
id bigint,
code text,
name text,
start_date date,
...
- 90.2k
0
votes
2
answers
4k
views
Postgresql split single row into multiple rows based on date [duplicate]
In my case, we have a table ABC with the supposed 1 row.
id
name
start_date
end_date
1
Rahul
01/01/2021
06/01/2021
but while retrieving I would like to get multiple rows where the start date is ...
- 30.6k
-1
votes
1
answer
92
views
How to execute join statement dynamically without using static group id? [closed]
I work on SQL server 2014 I need to run this statement below dynamically without
using group id static
because I don't know maximum group id using may be 10,15,20
so How to run query below dynamically ...
- 101
0
votes
2
answers
632
views
How to limit tree traversal in case of loops?
So I want to limit tree depth traversal (as the simplest recursion killer tool). Example code that turns normalized records into tree:
CREATE TABLE items (
item_id serial PRIMARY KEY,
title ...
- 14.4k
2
votes
1
answer
440
views
How can I change string output in a hierarchy tree with PostgreSQL?
I have a table like this:
-------------------------------------------------
| id | description | parent_id | cost
--------------------------------------------------
| 1 | Radiology ...
- 349
1
vote
1
answer
388
views
Define recursive composite types
Is it possible to define recursive composite types in Postgres 13?
create type "t" as (
"a" int,
"b" t
);
I am aware that I can use a table with a parent-child ...
- 182k