Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
1
vote
1answer
30 views

54001 stack depth limit exceeded in recursive stable function [closed]

I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I ...
1
vote
0answers
24 views

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the ...
1
vote
1answer
24 views

How can I create a recursive query over a column that has gaps?

I've adapted the recursive query from here in order to calculate ELO ratings (like in chess) from a list of game outcomes on-the-fly. Given a table of games like: id | home_player | away_player | ...
0
votes
1answer
27 views

How to use a recursive CTE to get ancestors in a hierarchy

create table division ( id serial primary key, name varchar not null ); -- nested sets table create table location ( id serial primary key, name varchar not null, division_id integer not ...
0
votes
2answers
62 views

How to recursively update a same string?

I have a table with a column containing names like this: id, employee 1, Mr. John Cole Thornton 2, Mr. Paul George Mckenzie 3, Mr. George Mick McDoughal 4, Ms. Emily Suzan Flemming 5, Mr. Alan ...
-1
votes
2answers
32 views

Start at condition and stop when criteria is met

i'm new to MS SQL and i have to carry out the following task. What i want to output is the time of the first occurence of Stateid = 4, go through the list and return the time when i meet a either ...
3
votes
2answers
33 views

Rolling sum aggregates with self-referencing conditions

I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments ...
0
votes
2answers
70 views

all the dependants of a stored procedure or function recursively - but shown only once

I often work with third party data professionals and they ask me to replicate whatever table(s) such and such stored procedure or function or view touches within one or more databases. so I have ...
0
votes
1answer
30 views

Recursion only if a criteria is met

I'm using DB2 V7 and I have a two table PARTLIST and PARTTYPE as follow : CREATE TABLE PARTLIST (PART VARCHAR(16), SUBPART VARCHAR(16), QUANTITY INT, ...
-1
votes
1answer
28 views

Product part Query (BOM) [closed]

I'm using DB2 V7 and i have a product table like below : select * from product; Product |Component|Quantity |Line number| --------+---------+---------+------------ Product1|Part1 |1 |1 ...
0
votes
1answer
33 views

How to have CTE query recurse parent query result one by one?

I have this query used on Postgres to return place administrative levels. WITH RECURSIVE hierarchy(name, pinyin, level) AS ( SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk from ...
5
votes
1answer
81 views

Grouping by overlapping arrays, transitively, without duplicates

I've found: Group by array overlapping https://stackoverflow.com/a/23650080/5419599 However, I'm having trouble putting it to use in my case. I have a table like so (the real myid values are hashes,...
1
vote
0answers
69 views

How to get all parents associated to a child, one row for each association [duplicate]

I have a table with two columns, Parent and Child. i need to get an output list showing a child and all associated parents, one parent per row. The youngest child will show as a child in rows equal ...
8
votes
2answers
448 views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
0
votes
1answer
35 views

write SQL query that takes its own output as input

In a table that displays how trades are related to each other, starting from one trade, I want to find all other trades that relate to the first trade: 713613 PostPTETradeIDs 718478 713613 ...
0
votes
0answers
18 views

Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance

I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and each Sheet can define different ...
0
votes
1answer
70 views

Exclusion constraint on recursive foreign key (on the example of a Tree)

I have the table with recursive foreign key. It's just hierarchical tree structure: CREATE TABLE tree ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value ...
6
votes
1answer
120 views

Tree Node Calculation - SQL Server

I'd appreciate some help with something I am trying to achieve please. The parts that my company make are built from sub assemblies and sub assemblies of sub assemblies. This looks like: ...
0
votes
0answers
26 views

SELECT query to read recursive relationship table

I have below MySQL database table which has a recursive relationship. A hospital maybe managed by another main hospital in this scenario. I have a difficult time trying to retrieve all data with name ...
1
vote
3answers
112 views

Count Items not In Time Range

I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out. So, I have this table: WITH ADates AS ( ...
2
votes
1answer
157 views

CTE Query to CONCAT text (over 10,000 rows)

I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber. Below is what I am working with: CREATE TABLE #SampleTable ( ...
0
votes
0answers
40 views

Parent/child table with include/exclude items

I have a table with parent-child relations. The relations can go n-level deep. There is also a table with elements that belong to a group. CREATE TABLE group_children( id serial PRIMARY KEY, ...
0
votes
0answers
45 views

Cleaner approach for Recursive CTE query

I have a table called entry_changes defined as follows: Table "public.entry_changes" Column | Type | ...
0
votes
1answer
230 views

Limiting Common Table Recursion

I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code. SELECT id,MK, pid,AFI FROM M A WHERE LEVEL <= 2 -- limiting iterations ...
2
votes
1answer
441 views

How to query all entities associated with all children (recursive) of a node in SQL?

This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary. Ok, I’m having trouble putting this into words… so bear with me. Say I have a tree of ...
3
votes
3answers
887 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 ...
1
vote
1answer
144 views

Rewrite SQL query in CTE query

I have this table which I would like to store a chain of records. CREATE TABLE table_name ( id INT, unique_id varchar, reference_id varchar, ); I want to implement SQL query for MariDB ...
1
vote
0answers
83 views

Is there a way to make postgres count planned number of rows when using recurcive CTE?

Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle: create table test_recursive ( id serial primary key, parent_id int null references test_recursive, name text ...
0
votes
2answers
435 views

How to get sum of last previous X days of everyday?

Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday. For example my graph will include Sum of Day - 60 to 30 Sum of Day - 59 to 29 Sum of ...
19
votes
4answers
3k views

How does SQL recursion actually work?

Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart. Consider the following simple example: ...
0
votes
1answer
28 views

Removing duplicate-ish paths generated from recursive CTE

I have some multi-parented data, that I run a recursive CTE against to find common ancestors: item_id |item_name |path_name |id_path_array | --------|----------|-------------|--------------| 1 ...
1
vote
2answers
183 views

Joining two ltree based tree tables recursively

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join ...
5
votes
2answers
1k views

PostgreSQL - retrieve all IDs in a tree for a given subnode

I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node. The table is very simple, just an join table with a parent id and child id. This is a ...
2
votes
0answers
43 views

Unrolling an entity-matching table

I have a table with the following structure: create table pairs ( block text, left_entity int, right_entity int ); That table was the result of a record linkage process. One of the ...
2
votes
1answer
118 views

WITH RECURSIVE with union among the recursive term

Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules: And I ...
1
vote
1answer
569 views

Distribute multiple payments to invoice lines

I'm having a problem allocating payments to invoice lines. We’re using MsSql 2014 Data looks like this: Invoice lines table (sales): lineId invoiceId value 1 1 100 2 ...
1
vote
0answers
131 views

Postgres recursive query not using trivial index on massive table

I've got a simple recursive query to find a tree of all posts: with recursive all_mentions(post_id, author_id, mentioned_id) as ( select post_id, author_id, mention_id from mentions where ...
2
votes
1answer
793 views

Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation

I have a table with Parent-Child Relation in the same Table. Some Sample Data id │ name │ parent │ is_active ════╪══════════════════════╪════════╪═══════════ 1 │ Company A ...
3
votes
1answer
462 views

How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
1
vote
3answers
587 views

SQL recursion and cte - dates timeseries

I need to build vector(s) of time series dates (say 10) for a subset of start dates. The following statement returns 10 consecutive dates starting from '2010-01-01'. ;with cte as (select 1 i union ...
1
vote
1answer
95 views

Sql query for all ascendent of a node

I have a table having records as below: ID Name parentId 1 A Null 2 B 1 3 C Null 4 D 3 5 E 4 I need out put in following format ID Name ParentName ...
0
votes
1answer
431 views

How make recursive json on mysql

my table looks like CREATE TABLE `test_comments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, `test_id` int(10) unsigned NOT NULL, `user_id` int(...
0
votes
1answer
819 views

How to select recursively in a child parent design situation (in MySQL)?

Let's take into consideration the following tables: CREATE TABLE actions ( id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, user_id BIGINT(20) ...
1
vote
0answers
181 views

How to get the hierarchy of particular employee using JPA spring boot?

I am trying to find a way to get the employee hierarchy in an organization using JPA and Spring boot. The problem is similar to the following link Similar Data Model If A reports to B and B reports ...
1
vote
1answer
81 views

How to link rows in a many-to-many relationship where types have parents, grandparents etc

In PostgreSQL 10.4, I have a model/table category with a Foreign Key to itself. The hierarchy can extend to any number of levels: parent, grandparent etc. I have a model/table product. Each product ...
0
votes
1answer
268 views

Geting rows from self-referencing single table using Postgres

I am trying to get rows from self-referencing single table in Postgres. I got table A and I would like to get Table B. For given ProcID using Input_proc_Id I want to get another row until i get ...
0
votes
2answers
72 views

Select starting node ID in CTE

I have following table: create table osoba( id int primary key, idNd int, nazwim nvarchar(500), ) Data looks like below: id idNd nazwim 1 NULL grandpa 2 1 child1 3 1 child2 4 2 ...
2
votes
1answer
2k views

Redshift- How to use previous row's calculations in current row

I am using redshift to determine projected inventory for next few week. I have receipts, current inventory and forecast. ei= ei(previous row) + receipts - forecast wk bi r f ei 1 100 20 ...
6
votes
1answer
830 views

Help with CTE Aggregating Children Recursively

I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised ...
1
vote
1answer
482 views

Recursive query to find shortest path in graph

I have an assignment to find the shortest possible path between two people in a table. The table is Company(company_name, job, name), all of them not null, but none of them unique (primary key is the ...