2,104 questions
1
vote
1
answer
112
views
Postgres Recursive Query Behavior
I have a table test with two columns, each referencing the other
ID1
ID2
2
1
3
2
4
3
5
4
Given an id, I need to traverse the chain to return all the rows traversing the chain. Below is the query I ...
- 325
0
votes
0
answers
32
views
recursive sum of totals by org and descendants in redshift
I have a table like this:
total
alert
group_id
hlevel
full_path
parent_id
root_group_id
5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000
1
BizA\DivA
5100FFFF-60B6-D5CD-BFBA-A8A3E03F0000
5100FFFF-60B6-D5CD-FCCD-...
- 1
-3
votes
1
answer
63
views
How to write recursive query for path generation and cycle detection?
I'm working with hierarchical tree data in DolphinDB and need to solve two specific programming challenges:
Generate full paths (concatenated IDs and names) for all nodes, including infinite-level ...
- 319
0
votes
2
answers
114
views
Nested Loop in SQL in BigQuery
I need to calculate few metrics for a period of days. The requirement is - For a given day, calculate the metrics based on the past 7 days. I need to output the metrics for every day for the past 2 ...
- 895
1
vote
1
answer
48
views
Traversing graph using recursive CTE - using "array" to store visited vertices
I'm studying graph traversal using recursive CTEs (learnSQL course). The question asks about visiting every city from a starting city. They use a "path" string built up from appended city ...
- 1,507
-5
votes
1
answer
68
views
Trouble with recursive query in redshift
Considering following test data:
CREATE TABLE products_su
(
country varchar(2),
intprd varchar(20),
period date,
su int
);
INSERT INTO products_su (country, intprd, "period&...
- 21
1
vote
2
answers
114
views
Multiple recurrence within redshift/postgres recursive query
I'm seeking help with including deeper recurrence then last row with recursive query. Source data:
CREATE TABLE products_su(country, intprd, "period", su)AS VALUES
('GL', 'Medicine', '2019-...
- 21
1
vote
1
answer
87
views
Recursive CTE with cycle detection using path array
I have a recursive CTE definition (graph) in Postgres that involves cycle detection: fiddle
CREATE TABLE relationships (
subject_type,
subject_id,
subject_relation,
resource_type,
...
0
votes
1
answer
89
views
Mysql slow recursive query with many join
I have a complexe query to make statistics on few tables.
I need, for each party and each thematic the number of vote between a member and a party + the number of vote with the same value (member_vote....
- 133
2
votes
2
answers
121
views
Recursive query to calculate folder size including nested files
I have a table in my Postgres 17 database where both files and folders are stored. If type = 'folder', then it represents a folder; otherwise, it's a file. By default, the size field is set for all ...
- 43
0
votes
1
answer
54
views
Update Postgres Pedigree process to prefix child decendents with @ character
I had assistance in creating a Postgres Pedigree process for plant breeding in 2012 via PostgreSQL Recursive via 2 parent/child tables. The pedigree parent/child hierarchy is defined by the family/...
- 131
0
votes
0
answers
58
views
MySQL 8.0 Query to MariaDB [duplicate]
I created the following query in MySQL 8.0, but when I try to add using phpMyAdmin I get the following error:
MySQL said: #1064 - You have an error in your SQL syntax; check the manual that ...
- 11
1
vote
1
answer
99
views
"Backwards recursive" query in PostgreSQL?
My question here is somewhat related to my (old and successfully answered) question here: Recursive count query in PostgreSQL. Now, I am facing a somewhat similar problem, which I am actually not ...
- 79
0
votes
1
answer
197
views
Amazon QuickSight | Recursive CTE in subquery are not supported
I would like to use a recursive CTE to generate a series of dates. I am using this CTE to cross join onto locations to generate a mapping table as a CTE where each location has a row for each date. ...
2
votes
1
answer
104
views
Recursive CTE to remove duplicates
I'm looking to clean up event data that happens to have "duplicate" rows for a given day. I want to remove rows for a day that have more than one status based on the context of the next day'...
- 413
-1
votes
2
answers
64
views
Translate excel formulas into Redshift SQL query
I am looking for help to translate excel formulas into Redshift SQL . With given fixed input values for rows 1 (Month ascending) and 2 (SU - some number) I have to calculate through particular cells ...
- 21
0
votes
2
answers
91
views
How to find the predecessor of the predecessor
I am trying to use an SQL-query to find the predecessor municipality of a municipality or, if available, other predecessor municipalities.
Here is my input table (MUNICIPALMERGE):
municipality_id
...
0
votes
1
answer
42
views
Recursive filtering JSONB with hierarchy in Postgres
Using Postgres JSONB for storing my data.
JSON hierarchy root -> sections -> chars. Here possibilities of sections contains child of sections.
Each level contains data objects.
I need filter the ...
- 21
0
votes
2
answers
164
views
How to use recursive SQL to find the parent ID
I'm a noob in the recursive domain in that I understand the syntax but I'm unable to apply it to my current problem. I'm using Postgre SQL.
I have a product_id 'ABC123' that has a parent category '...
- 1
0
votes
0
answers
35
views
Laravel recursive Category list with checkbox
How can I query the categories so that the parent and its related children are displayed in a single row?
Example datatbase:
id parent_id name
1 0 Category
2 1 subCat 1
3 ...
- 21
0
votes
1
answer
74
views
Identifying Overlapping Appointments with One Non-Overbooked Occurrence
Scenario:
Appointments are made with start and end times. Appointments are allowed to overlap, but for reporting purposes, we need to know when there are overlapping appointments (called overbooking) ...
- 13
0
votes
2
answers
74
views
Generate rows for an INSERT using recursion
I need to insert data into new table person_department using recursion.
My tables for now:
person
id group_id
800 10
805 21
department
id name group_id parent_id
1 ABC ...
- 113
0
votes
1
answer
75
views
CONNECT_BY_ROOT Inside of a CONNECT_BY_ROOT
I'm trying to combine two CONNECT_BY_ROOT queries. They work well individually. I've been trying to combine them such that one is a subquery inside the other, but I'm falling short.
Query 1 - ...
- 11
0
votes
1
answer
450
views
Complex BigQuery recursive CTE use case that joins on non-unique key and also unions at the same time
I have a complex (but highly interesting!) use case for BigQuery recursive CTE. I understand how to solve it conceptually but after hours of trying, I just can't get my head around exactly how to do ...
- 101
1
vote
3
answers
81
views
How can I calculate this conditional monthly limit?
In May client P003 had 600 of buy_amt (sum of sales in that period) and 1000 transfer_amt. I want to calculate the remaining limit (buy_amt - transfer_amt) but starting from top to bottom and the ...
