Questions tagged [recursive]
Using recursion to solve database problems.
262 questions
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,
...
0
votes
1
answer
116
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
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 ...
- 29
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
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 ...
- 127
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 ...
- 115
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 ...
- 3
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);...
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,...
0
votes
1
answer
532
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,888
2
votes
1
answer
764
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 ...
- 2,919
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
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) ...
- 163
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
2
votes
2
answers
3k
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
...
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-...
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
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 ...
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,
...
- 143
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 ...
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 ...
- 115
-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
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
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 ...
- 75
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 ...
- 37
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
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 ...
- 31
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
1
vote
1
answer
321
views
Order a nested table with path index according to list_order in SQLite
I have a SQL table with this nested structure:
CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER);
My SQLite version does not support window function,...
- 113
0
votes
0
answers
31
views
Fastest way of updating a spider web of data
I have a large table with over 60 million rows, which has relational data inside of it. I need to update each of these rows to the newest date, of the newest member, including members that that ...
- 1
3
votes
1
answer
5k
views
Recursively find the path to all leaves descending from a given parent in a tree
I'm trying to write a query to identify the path to each furthest descendant of a particular parent node in a table of tree structures like:
0 1
| |
2 3
| |
4 5
...
- 155
2
votes
0
answers
483
views
Drop intermediate results of recursive query in Postgres
I'm trying to aggregate the contents of a column from a directed acyclic graph (DAG) stored in a Postgres table.
Each dag row has an id, bytes, and may also have a parent referencing another row. I'm ...
- 155
1
vote
1
answer
154
views
Can CTE simplify repeated and possibly recursive joins?
I have three tables organized as depicted below, showing foreign keys and unique fields (green):
That represents email messages received by a server. (Sorry I couldn't simplify more. Full version ...
- 115
3
votes
3
answers
451
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 ...
- 133
0
votes
1
answer
36
views
Getting Indirect parents
I have following different tables
LeafChilds
ChildId, ChildName
1, Child1
2, Child2
Group
GroupId, GroupName
1, Group1
2, Group2
3, All
GroupChildMapping
GroupId, ChildId
1, 1
2, 1
2, 2
...
- 9
0
votes
1
answer
7k
views
Recursive CTE to get Grandparent, parent, children [duplicate]
I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents.
Here's an example of how things can look in the tree:
...
- 309
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
2
votes
1
answer
124
views
Org chart CTE with exact index but plan shows expensive sort node
I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to ...
- 255
1
vote
2
answers
3k
views
Update Column from Recursive CTE (MySQL/MariaDB) [closed]
I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table.
My code is below- ...
- 13
-1
votes
3
answers
3k
views
Is there really no SIMPLE way to achieve "recursive lookup" in PostgreSQL?
I have a table of categories:
id,label,parent_id
1,Animals,null
2,Dogs,1
3,Cats,1
4,Black,3
This means that "Animals" is a top-level category, and "Dogs" and "Cats" are ...
- 161
0
votes
3
answers
843
views
Recursive CTE with partition
I have a table like this in MS SQL SERVER 2014:
ID|Race|Lap
1 |21 |11
2 |21 |NULL
3 |21 |NULL
4 |21 |NULL
5 |29 |65
6 |29 |NULL
7 |29 |NULL
8 |29 |NULL
I am trying to fill up ...
- 476
3
votes
1
answer
2k
views
Recursive CTE Multiple Levels [duplicate]
I have a table showing which roles recursively grant access to which resources within a database. For example:
The Default_Role grants access to the App_Role, the App_Role grants access to the ...
- 43
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
1
vote
0
answers
80
views
Copying a dependency structure
I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or ...
- 11
2
votes
2
answers
1k
views
How to do a recursive sum of children in mysql?
I've searched and tried for long hours, and can't seem to get what I need.
I have a table of accounts as a tree -parents and children-, only leaf accounts can have transactions, any parent will NOT ...
- 165
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