# Questions tagged [recursive]

Using recursion to solve database problems.

257 questions
Filter by
Sorted by
Tagged with
24 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 ...
• 431
60 views

### What is the best way in Postgres to inherit values from parent row recursively if not provided?

Is there a pattern in SQL where a child row inherits empty (null?) values from a parent? E.g., given the following 'chickens' table (sorry): id parent_id name flys noise weight egg_color ...
42 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,683
97 views

### Convert numbers to words | Spell out DECIMAL USD 1259.75 as 'One thousand two hundred fifty-nine dollars and seventy-five cents'

ALTER FUNCTION [dbo].[NumberToWords](@Number DECIMAL(18, 2)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Words NVARCHAR(MAX) SELECT @Words = COALESCE(@Words + ' ', '') + CASE WHEN @...
1 vote
468 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 ...
24 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
218 views

### How to sum up the distinct Total Time of an Event ignoring duplicate overlaps in Times?

I have the following EventTimes table: DROP TABLE IF EXISTS dbo.EventTimes; CREATE TABLE dbo.EventTimes ( EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY, EventId INT NOT NULL, StartTime TIME NOT ...
• 38.1k
71 views

### SQL Pattern to get "root/ start" of uneven hierarchy dataset

I feel this is a common problem and I've seen it in some SQL challenges even but for the life of me, cannot think clearly about a solution. Say you have an uneven hierarchy. Elements that belong to ...
• 1,683
20 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
1 vote
78 views

### PostgreSQL recursive query performance improvement

I'm building a database which is used to derive hierarchical relations for which I chose a PostgreSQL recursive CTE . my table structure is table_name |column_name |data_type ...
• 13
37 views

### Finding tree contours using nested set model

Is there a query or algorithm that will enable me to find the left and right contours for general hierarchical trees and subtrees described using the nested sets model? I have described tree ...
• 131
61 views

### My recursive query runs with no end in sight. Any idea as to how to solve this?

With A as( select client_id, convert(date,admission_date) start_date, year(convert(date,admission_date)) start_year, month(convert(date, admission_date)) start_month from ...
163 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'?
370 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
234 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,849
217 views

### "ON UPDATE" in self referencing table

I have a table Category with a parent_id column referencing the self id column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too. At ...
1 vote
78 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
500 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 ...
• 431
2k views

### How to fix error "Line 25: The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

I added OPTION (MAXRECURSION 1000) at the very bottom but it was not enough. My query: Use DatabaseName; DECLARE @SOURCE_TABLE_NAME NVARCHAR(200) = 'WorkOrderVersion' DECLARE @SOURCE_SCHEMA_NAME ...
• 1
1 vote
185 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
538 views

### Schema-binding with recursive scalar UDF

TL;DR; Is it a bug that SQL Server allows a scalar UDF to recursively call itself when schema-bound, but only when altered to do so using the CREATE OR ALTER syntax? Or is it a bug that other syntaxes ...
• 13.3k
734 views

### From last id, recursively find parent id and fill column

CREATE TABLE t1 ( COL1 nvarchar(36), COL2 nvarchar(36), ExpectedResult nvarchar(36) ) INSERT INTO t1 (COL1, COL2, ExpectedResult) VALUES ('CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-...
• 33
322 views

### Return recursive self joins in PostgreSQL with aggregated data

I have this simple table schema: SQL Fiddle PostgreSQL 9.6 Schema Setup: CREATE TABLE IF NOT EXISTS users( id INT NOT NULL PRIMARY KEY, email TEXT NOT NULL UNIQUE, password TEXT NOT NULL, ...
• 165
684 views

### MySQL Select query for a calculated column that uses previous row value for current row

I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated. Table has 3 columns - Employee, Month, Salary. ...
161 views

### Why PostgreSQL RECURSIVE query iteration does not return the same result as manually created one?

I have the following test table: SELECT * FROM attempts order by id; id | user_id | name ----+---------+------ 1 | 1 | a 2 | 2 | b 3 | 1 | b 4 | 1 | c 5 | 2 | ...
• 121
1 vote
452 views

### How does PostgreSQL recursive query treat SELECT MIN(ID) FROM T on each iteration?

I have the following query that I do not understand how it works, especially the subquery that returns the minimum ID without grouping. WITH RECURSIVE t(id, user_id, name) AS ( select min(id) - 1, ...
• 121
214 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 vote
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 ...
231 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
197 views

### Optimising a recursive SQL query that processes several million records in BQ

I need help optimizing a recursive SQL query in BQ. I have hierarchical data stored in a table as parent-child relationships, i.e will be stored as parent_item_id child_item_id 1 1 1 2 1 3 3 4 ...
157 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 vote
794 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 ...
232 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....
292 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 ...
• 181
1 vote
1k 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 ...
• 181
57 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
849 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
148 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. ...
40 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 ...
• 103
978 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
248 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 ...
• 103
1 vote
59 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
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 ...
350 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 ...
1 vote
203 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,...
• 8,871
196 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, ...
110 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
114 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
251 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
1k 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 ...