Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
0 votes
1 answer
28 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);...
user avatar
0 votes
0 answers
31 views

Unique query to multiple tables with array. Is it possible?

I'm using PostgreSQL 14. Today, I have the following tables, with more than 4 million lines each table: tbx_00, tbx_01, tbx_02, tbx_03, tbx_04 and tby_00, tby_01, tby_02, tby_03, tby_04 About the ...
user avatar
1 vote
0 answers
29 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 ...
user avatar
  • 123
2 votes
1 answer
51 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 ...
user avatar
0 votes
0 answers
62 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,...
user avatar
0 votes
1 answer
26 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 ...
user avatar
  • 1,690
0 votes
0 answers
46 views

Oracle CTE recursive query in package doesn't compile

I have a query which does work alone, but when put into package, package fails to compile with error: [2022-01-19 19:21:30] 58:12:PL/SQL: ORA-00600: internal error code, arguments: [qctcrcol:dty], [1],...
user avatar
  • 155
2 votes
1 answer
85 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 ...
user avatar
1 vote
3 answers
172 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 ...
user avatar
  • 2,337
6 votes
3 answers
303 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&...
user avatar
  • 55.7k
2 votes
2 answers
272 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) ...
user avatar
4 votes
2 answers
313 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, ...
user avatar
0 votes
1 answer
198 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 ...
user avatar
0 votes
2 answers
81 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-...
user avatar
0 votes
1 answer
37 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 ...
user avatar
2 votes
1 answer
86 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 ...
user avatar
0 votes
1 answer
151 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, ...
user avatar
  • 123
3 votes
2 answers
114 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 ...
user avatar
0 votes
2 answers
801 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 ...
user avatar
  • 115
-1 votes
1 answer
84 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 ...
user avatar
4 votes
1 answer
311 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", "...
user avatar
  • 65
0 votes
2 answers
64 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 ...
user avatar
  • 65
2 votes
1 answer
184 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 ...
user avatar
  • 47
8 votes
2 answers
919 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 ...
user avatar
  • 866
0 votes
1 answer
90 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 ...
user avatar
  • 301
0 votes
2 answers
1k 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 ...
user avatar
  • 4,169
1 vote
1 answer
76 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,...
user avatar
0 votes
0 answers
23 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 ...
user avatar
2 votes
1 answer
746 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 ...
user avatar
2 votes
0 answers
143 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 ...
user avatar
1 vote
1 answer
74 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 ...
user avatar
  • 115
2 votes
1 answer
79 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 ...
user avatar
  • 121
0 votes
1 answer
33 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 ...
user avatar
  • 9
0 votes
1 answer
1k 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: ...
user avatar
  • 301
6 votes
1 answer
623 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 ...
user avatar
  • 249
2 votes
1 answer
72 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 ...
user avatar
1 vote
2 answers
732 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- ...
user avatar
-1 votes
3 answers
684 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 ...
user avatar
0 votes
3 answers
230 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 ...
user avatar
3 votes
1 answer
221 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 ...
user avatar
5 votes
1 answer
154 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 ...
user avatar
1 vote
0 answers
51 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 ...
user avatar
2 votes
2 answers
551 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 ...
user avatar
  • 155
5 votes
2 answers
193 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 (...
user avatar
2 votes
2 answers
149 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 ...
user avatar
0 votes
1 answer
54 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 ...
user avatar
2 votes
1 answer
490 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 ...
user avatar
  • 213
0 votes
1 answer
116 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 ...
user avatar
  • 9
10 votes
1 answer
850 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 =...
user avatar
0 votes
1 answer
478 views

How to aggregate over a "multi source" with recursive, in postgres?

Imagine I have a table "departments" like this: name TEXT parent_department TEXT (nullable) I also have a "budgets" table like this: department_name TEXT budget INTEGER I need to "traverse" this ...
user avatar
  • 220

1
2 3 4 5