Skip to main content

Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
0 votes
0 answers
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 ...
alx9r's user avatar
  • 431
0 votes
1 answer
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 ...
Sabrina Leggett's user avatar
0 votes
0 answers
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 ...
user45867's user avatar
  • 1,683
0 votes
1 answer
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 @...
Luis Avalos's user avatar
1 vote
2 answers
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 ...
James McGrath's user avatar
0 votes
0 answers
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/...
WilliamMayor's user avatar
3 votes
2 answers
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 ...
J.D.'s user avatar
  • 38.1k
0 votes
1 answer
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 ...
user45867's user avatar
  • 1,683
2 votes
0 answers
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 ...
surfmuggle's user avatar
1 vote
1 answer
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 ...
Arun's user avatar
  • 13
0 votes
0 answers
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 ...
Chris Walton's user avatar
-1 votes
1 answer
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 ...
Sam Johnson's user avatar
-2 votes
1 answer
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'?
Mahmoud fathy's user avatar
0 votes
1 answer
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 ('...
sudoExclamationExclamation's user avatar
1 vote
0 answers
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 ...
Manngo's user avatar
  • 2,849
0 votes
1 answer
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 ...
bguernouti's user avatar
1 vote
0 answers
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 ...
Nandakumar V's user avatar
8 votes
2 answers
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 ...
alx9r's user avatar
  • 431
0 votes
0 answers
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 ...
Yas  's user avatar
  • 1
1 vote
0 answers
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 ...
MM3000's user avatar
  • 11
8 votes
1 answer
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 ...
Charlieface's user avatar
  • 13.3k
3 votes
2 answers
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-...
JCP's user avatar
  • 33
3 votes
1 answer
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, ...
Sirjon's user avatar
  • 165
0 votes
1 answer
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. ...
Vineeth's user avatar
0 votes
1 answer
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 | ...
user1602's user avatar
  • 121
1 vote
2 answers
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, ...
user1602's user avatar
  • 121
0 votes
0 answers
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 ...
awsBi's user avatar
  • 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 ...
TheSQLGoat's user avatar
0 votes
1 answer
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 ...
user3621633's user avatar
0 votes
0 answers
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 ...
Roman Dryndik's user avatar
0 votes
0 answers
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 "...
AleksTr's user avatar
1 vote
1 answer
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 ...
Subin Benny's user avatar
3 votes
1 answer
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....
nicodemus's user avatar
0 votes
1 answer
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 ...
Rishav Ghosh's user avatar
1 vote
1 answer
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 ...
Rishav Ghosh's user avatar
4 votes
1 answer
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 ...
David's user avatar
  • 41
1 vote
1 answer
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 -- ...
ahron's user avatar
  • 813
2 votes
0 answers
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. ...
Viz Krishna's user avatar
0 votes
1 answer
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 ...
StormTrooper's user avatar
0 votes
1 answer
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 ...
ahron's user avatar
  • 813
0 votes
1 answer
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 ...
StormTrooper's user avatar
1 vote
0 answers
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 ...
Gthoma2's user avatar
  • 111
3 votes
1 answer
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 ...
Ramakrishnan Srinivasan's user avatar
0 votes
0 answers
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 ...
J Pablo F's user avatar
1 vote
3 answers
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,...
Jonathan Fite's user avatar
0 votes
0 answers
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, ...
Cloud Manager's user avatar
0 votes
1 answer
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/...
mreaglejr's user avatar
1 vote
0 answers
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 ...
Valeriy's user avatar
  • 119
2 votes
2 answers
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 ...
Nan0's user avatar
  • 29
3 votes
1 answer
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 ...
Robert Elliot's user avatar

1
2 3 4 5 6