164 questions
0
votes
2
answers
140
views
SQL query returns `ERROR: syntax error at or near "union"`
I am a beginner in SQL. What is the syntax issue with the query shown here?
I just needed the random rows to be present n number of times.
with recursive cte1 as
(
select 1 as idx, company_id, ...
- 21
0
votes
1
answer
89
views
Hexadecimal concatenation in recursive CTE is truncated to 64 characters
I'm working on Nested Set Hierarchies. I'm using a recursive CTE to build the nested equivalent of adjacency list table. MySQL 8.4 is my platform.
-- adjacency list table
CREATE TABLE dct_node_adjc
(
...
1
vote
1
answer
97
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
109
views
Recursive CTE 'Query Error: relation [] does not exist', PostgreSQL v17
Practicing some SQL from Danny Ma's case studies. Working on number 3, found here. The challenge question that I'm requires an iterative table of payments made during 2020.
I'm scaffolding up to my ...
1
vote
1
answer
173
views
Recursive CTE in Firebird for calculating aggregate costs in hierarchical data
Consider the following tables:
create table WORKS_WITH_BASE_COSTS
(
CODE integer not null,
BASE_COST decfloat default 0 not null,
constraint PK_WORKS_WITH_BASE_COSTS primary key (CODE)
);
...
- 19.1k
0
votes
1
answer
127
views
Recursive CTE returns duplicate rows
In SQL Server, I have a Transactions and a TransactionDetails table.
Transactions table has the following columns:
TransactionId
Amount
ProcessedDate
TransactionDetailsId
TransactionDetails table has ...
1
vote
1
answer
29
views
MySQL Recursive CTE to retrieve all Attached Nodes of a Table
I have a recursive structure with the following two tables:
tree represents a hierarchy of nodes where the top node has parent = NULL.
the items can be attached to any of the tree nodes, anywhere in ...
- 3,319
2
votes
1
answer
106
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
0
votes
1
answer
59
views
Understanding inner join in recursive CTE (MySQL)
The code below is from: https://www.freecodecamp.org/news/mysql-common-table-expressions/
Data:
CREATE TABLE
categories (
id int,
cat_name varchar(100),
parent_category_id int DEFAULT NULL
...
- 15
1
vote
1
answer
680
views
Getting error recursive reference to query must not appear within its non-recursive term in postgres
I am running this query:
WITH RECURSIVE base_record AS (
-- Base case: start from the initial defined term
SELECT
dt.id AS defined_term_id,
dt.name AS term_name,
1 AS depth,
ARRAY[...
- 1,087
0
votes
2
answers
92
views
Suppress Duplicates in SQL Server Recursive Query
I've got a data structure where a record represents a person. A person can have a relationship to another person. There are different types of relationship so it's possible that there can be multiple ...
- 199
0
votes
0
answers
81
views
MySql recursive query using pivot table (many-to-many relashionship)
I'm having a huge performance issue when I run recursive query. Here is my DB Structure:
users:
id(index)
name
1
A
2
B
3
C
4
D
5
E
user_user
user_id(index)
parent_id(index)
2
1
3
2
4
2
5
4
And this is ...
- 48
0
votes
1
answer
107
views
Sorting records for hierarchical relationships when there is more than one parent column
I have following table wherein 2 (can be more) foreign key columns define self-relationship between records of the table.
[Hierarchical records in my problem dataset]
Id
Name
Parent1__c
Parent2__c
...
1
vote
1
answer
85
views
Postgres use recursive CTE to find the root node
I have seen and read many posts on using a recursive CTE to get the hierarchy from a self-related table.
I want to get just the root node of an entry in my table. In other words, if I have a suburb ...
- 13
0
votes
2
answers
141
views
Querying a whole graph emulated with SQL Server tables
I have an SQL-Server 2019 database with 2 tables NODE and EDGE, where NODE consists of two columns ID (int) and NAME (varchar)
and EDGE consists of two columns PARENTELEMENTID (int) and CHILDELEMENTID(...
- 69
0
votes
2
answers
143
views
Mysql 8 recursive query with two mandatory parents
I am working with a hierarchy problem where the nodes have two parents and need get only the nodes where both parents are in the result.
In the happy world, this query would be perfect
WITH RECURSIVE ...
- 1,248
0
votes
0
answers
38
views
Is it possible to flatten a parent-child hierarchy table to a flat/wide table using recursive CTE (Snowflake SQL)? [duplicate]
I have a dumb/bad solution to my problem, but I have read the Snowflake docs on recursive CTEs (https://docs.snowflake.com/en/sql-reference/constructs/with and https://docs.snowflake.com/en/user-guide/...
- 565
0
votes
1
answer
83
views
Keeping a constant identifying value at the beginning of a recursively unioning cte
As part of a T-SQL project on a Microsoft SQL Server, I'm using a recursively unioning CTE to trace a series of child parent relationships through some hierarchical data to pull each individual ...
- 3
0
votes
1
answer
63
views
Recursive way of finding parent and children for each row
I have a table with client_id and parent_clientid. Each client can be a parent and can contain multiple children. I would like to know, for each row, how to get the associated parent and children in a ...
2
votes
1
answer
82
views
clean ways to perform calculations with values sourced from nested groups of varying depths?
I would like to use the below tables to calculate z:
item
value
a
6
b
8
group
source_item
weighting
x
a
1
x
b
0.25
y
a
1
group
source_group
weighting
y
x
0.5
z
x
1
z
y
1
As a human, I'd solve by ...
- 23
1
vote
1
answer
75
views
recursive CTE with two child column
basically I have a audit table like the following.
JOB_NO
DEPENDANT_JOB_NO
DEPENDANT_SUB_JOB_NO
Job_198
Job_551
BDP2_Sjob_48
Job_24
Job_551
BDP2_Sjob_23
BIL_Job_10
Job_5
Sjob_6
I need to develop a ...
- 31
0
votes
0
answers
81
views
SQL, Recursive query to get ancestors with some rules of all parents
ID parent_id name is_public
------------------------------------------------
1 null category 1 0
2 1 category 2 0
3 2 ...
0
votes
1
answer
140
views
Optimise recursive CTE or rewrite to PL/pgSQL LOOP
I've got a procedure with recursion in Postgres 13 version.
It makes orgstructure from Vice to simple employer
with recursive relations_recurs(
pos_id, boss_pos_id, level_num, link_type, ...
- 309
0
votes
1
answer
135
views
Issues implementing a recursive CTE that updates a column(postgres)
I have an original table in this format
WHLO
ITNO
RESP
SUWH
36P
F379194
Jasmijn
36E
F379194
Mounish
36P
W33
F379194
Kneza
36E
T44
F379194
Fatin
36E
32P
F379194
Mari
R55
R55
F379194
Oumaima
What i want ...
- 1
0
votes
1
answer
1k
views
With recursive cte query in MySQL
CREATE TABLE `comments` (
`comment_id` int NOT NULL,
`user_id` int DEFAULT NULL,
`comment` varchar(32) DEFAULT NULL,
`post_id` int DEFAULT NULL,
`parent_comment_id` int DEFAULT NULL,
PRIMARY KEY (`...
- 297
-3
votes
1
answer
101
views
Parent Child table with "N" number of levels [closed]
This query:
SELECT pcn.id,
pcn.config_id,
pcnc.nombre_nivel,
pcnc.orden_nivel,
pcn.nivel_padre_id,
pcnc.empresa_id,
pcnc.proyecto_id,
pcnc.activo AS ...
- 57
0
votes
2
answers
109
views
SQL recursive cte - sort overlapping range
I have a table like below
range_from range_end created_on
100 105 2023-01-01
106 110 2023-01-01
106 111 2023-06-01
120 130 2023-01-01
112 130 2023-06-01
I need to clean it to get a most updated ...
- 3
1
vote
1
answer
121
views
Delete rows based on age, status and existence of related rows with recursion in PostgreSQL
create table the_table (
id integer, root_id integer, parent_id integer, status text, ts timestamp, comment text);
insert into the_table values
(1, null, null, 'COMPLETE', now()-'92d'::interval, '&...
- 494
1
vote
1
answer
86
views
Find nth parent that matches some condition
I have a table that has a structure similar to the following:
Id
Parent Id
Condition
1
0
0
2
1
0
3
0
0
4
3
0
5
3
0
6
3
1
7
6
0
8
6
1
9
8
0
10
9
0
where Condition is a bit that indicates whether the ...
- 13
0
votes
1
answer
43
views
Postgres: query average landed cost from prior records
I have a query from my postgres tables that looks like this:
date
sku
incoming_unit_qty
incoming_unit_cost
landed_unit_qty
2023-08-01
sku-1
1,000
$3.00
0
2023-08-01
sku-2
2,000
$2.00
0
2023-08-01
sku-...
- 81
0
votes
1
answer
87
views
Calculate cumulative sum over partitions with gaps in SQL
I have a table with stock changes with different countries and different products.
The table looks like the following:
Date
Country
Product
Variation
2023-10-01
Spain
Pen
1
2023-10-01
Germany
Pen
1
...
- 189
0
votes
2
answers
121
views
Multiple column Tree Hierarchy (Manager, Employee) in Oracle Sql
I have an employees table as below :
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID
103
Alexander
Hunold
AHUNOLD
590.423.4567
38720
...
-4
votes
1
answer
520
views
need to know about the recursive view in snowflake
I'm new to snowflake. And I need to know about the recursive view in snowflake. Can someone explain it with an example. And why recursive views are used. Non-Materialized view only
I need a really ...
- 15
0
votes
2
answers
248
views
Sum a column using Recursive query in Redshift
Question
I've got a Redshift table which contains hierarchal data by month. I am trying to calculate sum for manager based on people under them but struggling to get the SQL working. Actual data ...
- 102
0
votes
1
answer
87
views
Numbered days back from given date, only including certain date ranges
I have a temporary table #input which holds an ID and a calculation date cdate. I have another temporary table #service which holds a service record for each ID. The field ‘countable’ on the #service ...
- 36
1
vote
1
answer
1k
views
Having a single recursive CTE while there are multiple CTEs in a query
I am not sure if this has been asked before but I have searched across different forums and haven't found the exact answer. Should we always initialize the word RECURSIVE to the first CTE (even though ...
- 23
1
vote
2
answers
343
views
How to pull hierarchical parents till the root parent without traversing redundant paths?
I have a recursive SQL query that traverses a tree-like structure using a table variable. The query works correctly, but I'm facing performance issues. The idea to pull hierarchical parents till the ...
0
votes
0
answers
66
views
Handling Stock Adjustment in Recursive CTE for Recipes with Shared Ingredients
I am working on a SQL Server 2016 database where I have recipes and ingredients stored in separate tables. The relationship between recipes and ingredients is stored in a junction table. I am using a ...
0
votes
1
answer
528
views
Why does a simple recursive CTE make PostgreSQL resort to an external sort?
Using PostgreSQL 14, I'm working on a table workplaces which models a tree of workplaces. Besides attributes like country_code, each workplace identifies its parent workplace via a parent_id foreign ...
- 95.2k
0
votes
0
answers
39
views
Mysql - "With recursive" not working with PHP [duplicate]
I'm trying to print from a db the categories in a hierarchy order. I have developed a mysql code that sort of works in dbfiddle (https://dbfiddle.uk/_oUW-eW_), but I am not able to make it run in PHP.
...
- 19
0
votes
2
answers
434
views
Using a Table Valued Function inside a Recursive CTE
I am trying to use a recursive CTE to combine multiple calls to a table valued function.
I have a fiddle at: https://dbfiddle.uk/kktqHivz
I have a table called sales:
create table sales (
date ...
- 17k
-1
votes
1
answer
56
views
Finding salary of all the employee of all the supervisor where supervisor can have supervisor
Problem
I have two tables. employee and salary.
For the employee table I have employee_id and supervisor_id.
employee_id
supervisor_id
0
5
1
5
2
5
3
6
4
6
5
7
6
8
7
NULL
8
NULL
For the salary table I ...
- 2,745
0
votes
2
answers
629
views
Recursive CTE for a family tree keeps going into an infinite loop
I am trying to design a RDBM model for a family tree using SQL Server and currently I have the 3 tables that looks somewhat as follows
Members - Stores basic details of members of the family.
---------...
1
vote
1
answer
90
views
MySQL validating parents by children
I am programming in Visual Studio, but I need to perform validation on mySQL (v8.0.28) table and I guess it would be faster if it will be performed as procedure on server. Unfortunatelly, my MySQL ...
- 23
0
votes
1
answer
103
views
Recursive CTE Pivot for rolling count in SQL Server?
Basically I have a table of membership dates and status changes and I want to create a rolling count of each number of members by status for each date like:
And I want to output it like:
Not really ...
- 13
0
votes
1
answer
21
views
Recursive CTE- Gives me dupes of 3 rows
I am trying to find a group account and populate the aggregation of its child accounts QTY and Market value using CTE and Recursive CTE.. it gives me the correct result 3 times.. Not sure what i am ...
- 61
0
votes
1
answer
97
views
SQL: Generate Record Per Month for 1st of every month In Date Range
I have below source table - TEST_TAB
DP_AD_ACCT_NBR
DP_AD_CCY_CDE
DP_AD_CURR_BAL
DP_AD_LST_MDFSN_DATE
10001
REL123
100
2014-11-18
10001
REL123
174
2018-03-04
10001
REL123
145
2022-12-21
10001
REL123
...
user20984880
2
votes
2
answers
529
views
Using recursive CTE to generate hierarchy results ordered by depth without the use of heiarchyid
I would like to query hierarchy results ordered by depth first without the use of SQL's heiarchyid built in function. Essentially, I am hoping to accomplish the depth ordering without any fancy ...
- 193
0
votes
0
answers
105
views
recursive moving average with sql
supose we have the next table:
table example
and what i need is:
frst iteration: calculate the moving average 5 days before the last day including the last day = (2+1+2+3+4)/5 = 2.4 and "save&...
2
votes
1
answer
2k
views
Using string_agg() over n rows of data grouped by a Common Key
I would like to use string_agg(column_name,',') on some IDs in a column from a SQL result set. I would be grouping by a single key, but also want to make sure each grouped set results in n rows being ...
- 1,767