Questions tagged [recursive]
Using recursion to solve database problems.
253
questions
3
votes
2
answers
148
views
Can performance be improved by breaking databases up? [closed]
Performance is my over-riding concern, as I have a heavily recursive CTE that is the engine room of my application. This CTE may have to work on 4 columns of up to 7 million rows. Queries can take ...
- 31
15
votes
2
answers
6k
views
PostgreSQL Recursive Descendant Depth
I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
- 151
3
votes
1
answer
963
views
Postgres plpgsql: Is a view better than a select query in a plpgsql function
I have a recursive query to the general effect of:
WITH RECURSIVE text1 (selfid, parentid, text) AS
(
SELECT DISTINCT
text.selfid,
text.parentid,
text.text
FROM
text
WHERE
text.selfid = ...
- 183
0
votes
2
answers
2k
views
Single query for retrieving a category path
I have the following table structure and content:
+-------------+-----------+------------+
| category_id | parent_id | name |
+-------------+-----------+------------+
| 1 | 0 |...
- 101
1
vote
1
answer
907
views
Remove while loop from query inserting x values
I want to make this query set based and remove the while loop for inserting.
I am passing in a TVP to the stored procedure with the type and quantity required. Because TVP needs to be READONLY, I am ...
- 121
1
vote
1
answer
97
views
Need a sum on my current column
On an Oracle database, I need to calculate something like this - on column my
id yr m v1 v2 v3 st my
1 2015 10 1 0 0 0 0
2 2015 10 0 0 2 1 4
3 2015 10 2 0 0 1 0
4 ...
- 349
0
votes
0
answers
219
views
Oracle Database - Recursive sum?
I have an Oracle db and a table and I'd want to sum the VAL column until I have the value in column SUMM 100 and the difference in column DIF - as in this example:
ID VAL SUMM DIF
1 40 40 ...
- 349
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 |...
- 143
3
votes
1
answer
524
views
Need for recursive lookup, stuck in query design
In PostgreSQL 9.4, here is my table:
with r(pk, userid,partneruserid) as
(values
(1,1,2),
(2,1,3),
(3,1,6),
(4,2,5),
(5,2,6),
(6,3,1),
(7,4,1),
(8,5,3),
(9,6,2),
(10,6,3)
) select * from r;
In a ...
- 207
1
vote
2
answers
946
views
Include parent node ID in hierarchical recordset of child rows
I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a ...
- 133
2
votes
1
answer
4k
views
Count children of a parent in recursive CTE
I use this query to find comments by a parent id:
WITH RECURSIVE cte (id, content, path, parent_id, depth) AS (
SELECT id,
content,
array[id] AS path,
parent_id,
...
- 167
6
votes
2
answers
3k
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 ...
5
votes
1
answer
3k
views
Count the nodes in a binary tree structure
I need to count the left and right nodes in a binary tree structure (output grouped by joiningDate), given a particular starting node parent id (pid).
The tree is stored in the table shown below:
...
1
vote
1
answer
2k
views
Select tree-based structure
I have a table in my SQL Server database with data like this:
ID ParentID
--- ---------
1 NULL
2 1
3 1
5 1
5 4
5 6
8 6
7 9
9 10
I need a select statement that will return all the ...
- 1,451
2
votes
2
answers
3k
views
Retrieve additional columns in recursive CTE
This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep.
I manage ...
- 167
2
votes
2
answers
7k
views
How can I count all children & grandchildren until the 7th level?
I have 3 tables right now
1. members
2. members_parents
3. members_status
How can i count the children & grandchildren until to the depth level and separate the count() for the status 0 = ...
- 21
4
votes
0
answers
945
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-...
- 41
2
votes
1
answer
1k
views
SQL Server : create dynamically queries to select all related data in DB based on entry table and ID
I'm tasked to create a SQL statement which will create (and run?) multiple other queries which will select all related data outgoing from an given ID (uniqueidentifier) and a given table name.
It ...
- 21
2
votes
2
answers
154
views
Recursive Manager - Oracle 10g x 11g
We have a problem adapting a recursive SQL from 11g to 10g.
Oracle 11g Query ( Working as expected )
WITH ADRECURSIVEUSERLEADER (CDLEADER,CDUSER,NMUSER,FGUSERENABLED)
AS
(
SELECT US.CDLEADER AS ...
- 53
11
votes
2
answers
3k
views
Conceptual ERD Multi-table many to many, or possibly recursive?
I'm creating a conceptual diagram [yes, I know I've included attributes and keys - but this is just for me to consolidate what I'm doing whilst learning] -- so please treat it as Conceptual with the ...
- 213
10
votes
2
answers
2k
views
Finding all joins required to programmatically join a table
Given a SourceTable and a TargetTable, I would like to programmatically create a string with all joins required.
In short, I am trying to find a way to create a string like this:
FROM SourceTable t
...
- 890
1
vote
3
answers
3k
views
Is it possible to change recursion level of a CTE in a function?
I have created a function that takes a string and it returns a table with the position and character of each letter in the string. However as CTE recursion is limited to 100 levels, it fails if a ...
- 503
4
votes
1
answer
2k
views
Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion
Before posting, I have looked for a good solution on internet and I have coined this post for 3 days in row and trying to think through it prior this post. So if I have missed something or got it ...
- 143
2
votes
1
answer
2k
views
Finding the end of a Relationship chain optimally
I have a table that contains an id column, a parent column, and it's child column. So for every record, I know what record comes before it and what will come after it in a chain relationship, but from ...
- 503
14
votes
4
answers
70k
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 |
+----+-----------+
...
- 251
1
vote
1
answer
467
views
Parent-child Relationship with data scattered between these records
I have a 60 column query that fills in a card on an application. The problem I am running into is gathering all the parent-child relationship data into one record per child.
As a quick example: The ...
- 167
2
votes
1
answer
3k
views
T-SQL CTE Compare previous non-NULL and current row to filter out by difference value
My sample database (in fact a CTE statement data) looks like this:
eventdate val
2012-03-23 3965
2012-03-26 3979
2012-03-27 3974
2012-03-28 3965
2012-03-29 3967
2012-03-30 3959
2012-04-02 ...
1
vote
1
answer
106
views
Finding the current prices for n Fuelstations at a certain point in time
I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database.
CREATE TABLE public.de_tt_priceinfo (
id integer NOT NULL DEFAULT nextval('...
- 298
1
vote
1
answer
1k
views
Determine existence in recursive working table
I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is ...
- 555
0
votes
2
answers
232
views
Hierarcy Query Mysql [duplicate]
I have a table like this...
==========================================================================
UID || PARENT ID || SPONSOR ID
===============================================...
- 101
1
vote
1
answer
369
views
What Is the Next Best Ingredient to Add?
I am building a program that allows users to optimize their grocery shopping so they can make the most recipes using the fewest ingredients.
One of the features of this program is a function I’m ...
- 11
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 ...
- 1,089
3
votes
0
answers
52
views
Why does retrieving fewer rows take longer? [duplicate]
I have a view that (left outer) joins multiple recursive CTEs. There are about 10k rows. The results of running SELECT * FROM MyView ORDER BY MyPrimaryKey, with statistics time and io on:
SQL Server ...
- 131
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 ...
- 213
1
vote
1
answer
764
views
hierarchical retreival of records in postgresql
I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it.
PRODUCT MASTER ...
- 113
4
votes
1
answer
950
views
Why does Recursive CTE estimate just 1 row?
Given two cascading, self-contained (no real tables) recursive CTE's:
create view NumberSequence_0_100_View
as
with NumberSequence as
(
select 0 as Number
union all
select Number + 1
...
- 2,066
3
votes
1
answer
376
views
PL/pgSQL function or rCTE to detect depth of relations between two tables
I need to perform a PostgreSQL Function for checking the depth of relations. There is a circular binding from table a to b, back to another element in a and, in some cases back again to b. These ...
- 31
0
votes
1
answer
1k
views
Oracle Hierarchical query: with two node attributes NodeId and NodeType
I have the following use case where I want to make use of hierarchical queries to get the desired result.
In my use case I have two types of node say 'A' and 'B'. So the unique identifier of node is ...
- 103
1
vote
0
answers
268
views
How can I reduce recursion in Couchbase?
Sometimes, you need to access a certain document (A), but you do not know the key for that document (A), as that data is stored in a different document (B). So now, if you want to fetch A, it looks ...
- 121
2
votes
1
answer
4k
views
Recursive calls in stored procedure and temporary tables
I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to ...
- 33
0
votes
1
answer
16k
views
Fetch all parents from child id
I created a table with name "vegs" and inserted values just like this.
id name parent
--------------------------------------
1 fruits 0
2 Apple 1
3 ...
- 29
2
votes
1
answer
2k
views
Intentionally recursive instead of insert trigger does not want to nest
I have been struggling to get an instead of trigger to recursively call itself.
The idea is that when a row gets inserted into my table the trigger is placed on, based upon certain conditions ...
- 133
7
votes
2
answers
5k
views
Query to find circular references
I have two tables
ID Task
1 1
2 2
3 3
4 4
Col1 depend
2 3
2 4
3 1
4 2
ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
- 111
18
votes
1
answer
13k
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 ...
- 515
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 ...
- 43
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 ...
- 578
2
votes
2
answers
631
views
SQL Server - Recursive "find all FK connections for ID through the entire DB" query
I'm dealing with a DB with hundreds of tables, with interconnecting FK references producing potentially thousands of different permutations for how for example table A and table K can join together. ...
- 1,813
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 ...
- 506
0
votes
1
answer
1k
views
Flatten recursive hierarchy
I have the following recursive query:
SELECT SYS_CONNECT_BY_PATH(object_name, '\') AS path
FROM user_group_nodes
START WITH parent_id = '2892107544AA2278166C1AA8D123E761'
CONNECT BY parent_id = PRIOR ...
- 131
2
votes
1
answer
2k
views
Tree structure hierarchical query sorting
I have the following structure of table.
CREATE TABLE table1
(
serial_num integer NOT NULL,
parent_num integer,
parent_key text,
key_ary_num integer[],
level integer,
rank integer,
...
- 33