Skip to main content

Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
6 votes
2 answers
4k views

Improve performance for order by with columns from many tables

Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
Bremer Rodrigues's user avatar
2 votes
1 answer
14k views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
oucil's user avatar
  • 506
33 votes
2 answers
12k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
Tom Hunter's user avatar
  • 2,169
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", "...
Blender's user avatar
  • 75
1 vote
1 answer
464 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) ...
David Allen's user avatar
20 votes
5 answers
9k views

How to recursively find gaps where 90 days passed, between rows

This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this. SELECT ...
Independent's user avatar
19 votes
1 answer
14k views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
JohnCand's user avatar
  • 525
17 votes
2 answers
15k views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
markokstate's user avatar
14 votes
4 answers
72k views

Getting all descendants of a parent

I have a table with two columns, Parent and Child. I need to get the list of all descendants associated with the parent records. Source Table +----+-----------+ | Parent | Child | +----+-----------+ ...
lourdh's user avatar
  • 261
10 votes
1 answer
3k 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 =...
Sunny Patel's user avatar
10 votes
2 answers
3k views

self-joins in recursive CTE: `recursive reference must not appear more than once`?

This is tested with PostgreSQL 9.6, but it's a general SQL question. Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)? I tried the following rCTE containing a self-...
tinlyx's user avatar
  • 3,620
9 votes
2 answers
7k 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 ...
Luca Nitti's user avatar
7 votes
1 answer
4k 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 ...
cis's user avatar
  • 469
7 votes
1 answer
2k views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
user4150760's user avatar
  • 1,099
6 votes
3 answers
1k 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&...
Evan Carroll's user avatar
  • 63.8k
5 votes
2 answers
8k views

Create JSON object from recursive tree structure

Having this simple many-to-many self-referential structure. An item owns other items through the joins table: CREATE TABLE items ( item_id serial PRIMARY KEY , title text ); CREATE TABLE ...
Ben's user avatar
  • 179
4 votes
0 answers
967 views

Recursive BOM (Bill Of Material) type query

I have what I've been told is a Bill Of Material type query - which requires a 'recursive' query. I've never heard of such a thing - and was hoping to get some help. My schema stores asset / asset-...
Dave's user avatar
  • 41
4 votes
1 answer
2k views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
Eric Koslow's user avatar
4 votes
1 answer
5k views

Making a recursive CTE like query, but for many records

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code : declare @Id integer ;WITH CTE AS ( SELECT ...
PAP's user avatar
  • 43
3 votes
1 answer
5k 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 ...
Jason Dreyzehner's user avatar
3 votes
1 answer
781 views

Get the total count of every child foreign keys

I have been trying this in SQL EXPRESS 2016. Here is my problem: I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table. ...
CromixPT's user avatar
2 votes
0 answers
433 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 ...
Jason Dreyzehner's user avatar
1 vote
1 answer
2k views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
AbuAbdoh's user avatar
  • 588
1 vote
1 answer
2k views

Social network comments and posts model in PostgreSQL 9.6

I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post. The thing is that each ...
user avatar
0 votes
2 answers
4k 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 ...
Rahul's user avatar
  • 115
0 votes
0 answers
355 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
0 votes
5 answers
4k views

Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?

Example you have the following table CREATE TABLE emp (id,name,salary,manager) AS VALUES ( 1 , 'james' , 10000 , null ), ( 2 , 'alex' , 5000 , 1 ), ( 3 , 'Alice' , 4500 , 1 ), ...
HoneyBadger786's user avatar