All Questions
101 questions
0
votes
1
answer
61
views
PostgreSQL CTE Recursion - How to Improve Performance for Parent->Child Tree (8 levels deep, 890 roots)
I am working on a query that performs PostgreSQL CTE Recursion in PostgreSQL 13. This query is intended to recurse from the root nodes to the deepest leaf node with certain properties (labeled 'begat'...
- 50
2
votes
1
answer
79
views
Recursively sum child's nominal to parent, on dynamic level parent-child structure
i have data look like this :
i try to sum the Nominal column to each respective parent, so it will looks like this :
i provided the sql script for the data :
SET ANSI_NULLS ON
GO
SET ...
0
votes
1
answer
79
views
When generating combinations using a CTE, is there a way to use an index to group the elements of each combination?
I have a project that involves combining a variety of things into their possible combinations. This includes permutations, combinations, with and without repetition, and some more exotic ways of ...
- 439
0
votes
0
answers
171
views
Recursive CTE hiearchy snowflake -- join/ expand outward (columns) instead of rows?
I'm doing a dependency mapping explosion (like parts explosion).
I did a typical recursive CTE with a union all.
It looks like
with CTE as
( select abc from myTable where start_point = X
union all
...
- 1,709
1
vote
2
answers
547
views
How can I optimize a recursive CTE inside a IVTF?
I have a recursive CTE inside a inline table-valued function. The ITVF is returning a list of IDs containing a long sequence of ancestors for a person, it usually loops back about 12 to 18 times ...
- 121
0
votes
0
answers
35
views
How to match rows from relationship via a recursive CTE
Apologies for the terrible question title, I couldn't think how to describe this question in a single line.
I have a schema that looks roughly like this: https://dbdiagram.io/d/...
- 163
2
votes
0
answers
23
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 ...
- 219
-2
votes
1
answer
211
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
532
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
318
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,919
1
vote
0
answers
126
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 ...
- 121
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
1
vote
0
answers
212
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
0
votes
0
answers
225
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
50
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
281
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
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 ...
- 67
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
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 -- ...
- 813
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. ...
- 99
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 ...
- 813
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
384
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
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
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
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
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-...
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
-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
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
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
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
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
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
2
votes
2
answers
578
views
Recursively get ids of tree
I have a table foo.
So we have a hiearchical object structure where on foo has one parent (or none) and a list of children.
What I want is a sql statement that lists all id:s that can be found either ...
- 664
0
votes
1
answer
198
views
A way to process a CTE once?
I have a view that has serious performance issues, because I am linking it to a CTE that uses another view, with the same table references.
WITH
splits(ID, [NEW TEU])
AS
(SELECT s.[DTL ID] AS ...
- 295
2
votes
1
answer
1k
views
Why is my recursive CTE so much slower on Azure SQL?
I have this simple recursive CTE for a hierarchy of folders and their paths:
WITH paths AS (
SELECT Id, ParentId, Name AS [Path] FROM Folders
WHERE ParentId IS NULL
UNION ALL
SELECT ...
- 213
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 ...
- 9
1
vote
1
answer
510
views
Recursive Parent-Child Location Relationship Cache as View
Here is my data structure:
CREATE TABLE Locations(
LocID int IDENTITY(1,1) NOT NULL,
LocationTypeID int NOT NULL,
ParentID int NULL,
LocNum varchar(50) NULL,
LocName varchar(250) ...
- 127