A Recursive Common Table Expression in SQL is a Common Table Expression (CTE, also known as "WITH clause") that refers to itself recursively. The temporary result set is usually computed by iteration. Use this tag for questions concerning the definition and use of recursive CTEs.
Questions tagged [recursive-cte]
133 questions
0
votes
1 answer
SQL: Generate Record Per Month for 1st of every month In Date Range
I have below source table -…
user20984880
0
votes
0 answers
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" this result, that result will be a prediction for the…
0
votes
1 answer
Using multiple recursive CTE's to go backwards and forwards through a list (PostgreSQL)
PostgreSQL 14
Assume I have the following table:
CREATE TABLE links
(
linkid serial primary key,
patientid integer NOT NULL,
linkto integer NOT NULL
)
INSERT INTO links (patientid, linkto)
VALUES (1,2), (1,3), (1,4), (1,5),…
Alan Wayne
- 5,122
- 10
- 52
- 95
0
votes
1 answer
Display full Product Category Path using HierarchyID and Recursive CTE in SQL Server
I have only found this using a classic ParentID foreign key method. I wonder how to display full hierarchy path using the HierarchyID data type in SQL Server 2019.
I have this setup (simplified for this example):
CREATE TABLE tbl2ProductCategories…
ThomassoCZ
- 73
- 6
0
votes
0 answers
How to make groups in MSSQL tree with CTE
I have a problem with creating tree with my entities.
There are entities with entity id which are connected with other entities by parent_ent_id.
My input…
koxermaxerelo
- 1
- 1
0
votes
1 answer
traversing recursive CTE to the root in SQL Server reaches maximum recursion
I have a typical set of employee and the corresponding manager as most recursive CTE tutorials use. I took it from Uri Dimant's answer in tutorial.
Unlike depth-first search, my goal is to input an employee ID, and the query returns the list of…
tmo
- 117
- 2
- 8
0
votes
1 answer
How can I test the recursion before a save to the database to see if I get a recursion error?
I use the code below to gather data to populate a tree control in my software. Each company can have an 'owned by' reference added which helps me link one to another. If someone selects the wrong 'owned by' company and chooses one already listed…
Derek Jee
- 147
- 1
- 14
0
votes
2 answers
Recursive CTE in PostgreSQL for knapsack problem
I have a dataset with 3 columns:
Item_id
Sourced_from
Cost
1
Local
15
2
Local
10
3
Local
20
4
International
60
I am trying to write a query in PostgreSQL to fetch total of local and international items, customer can buy within the…
Rekha Gautam
- 65
- 4
0
votes
1 answer
Recursive CTE on every row in a stored procedure with parameter
I have a table called AccountNode.
Where the the column nodeId has a parent in the ParentNodeId col.
AccountNode
nodeId | ParentNodeId | Flag | SetId |
1 2 N 1
2 115 N 1
115 4 …
Lavina Khushlani
- 509
- 2
- 7
- 14
0
votes
2 answers
How to count distinct a field cumulatively using recursive cte or other method in SQL?
Using example below, Day 1 will have 1,3,3 distinct name(s) for A,B,C respectively.
When calculating distinct name(s) for each house on Day 2, data up to Day 2 is used.
When calculating distinct name(s) for each house on Day 3, data up to Day 3 is…
jasonlcy91
- 454
- 6
- 14
0
votes
1 answer
Recursive CTE in MySQL for missing dates
I have two tables DT and averages:
CREATE TABLE averages (
`workdate` DATE NULL,
`emp` VARCHAR(45) NOT NULL,
`Score1` INT NULL,
`Score2` INT NULL);
INSERT INTO averages…
Priya Patel
- 31
- 4
0
votes
1 answer
MySQL recursive cte base case
I have a table of events for a bunch of aggregates (order is one of them), that I can query like
SELECT * FROM events WHERE aggregate_id = :order_id AND aggregate_type = :order_type
This works fine for the simple case, but it gets a bit more…
Marian
- 3,789
- 2
- 26
- 36
0
votes
1 answer
Recursive CTE in EF Core + Automapper ProjectTo
In my project I have types Comment and CommentDto:
public class Comment
{
public Guid CommentId { get; set; }
public string Content { get; set; }
public Guid PostId { get; set; }
public virtual Post Post { get; set; }
public…
Boris Makhlin
- 240
- 5
- 11
0
votes
0 answers
Unexpected analytic function output in common table expression
In SQL Server 2019, analytic functions are not returning the results that I would expect in the context of recursive common table expressions. Consider the following non-recursive T-SQL query:
WITH SourceData (RowNum, Uniform, RowVal) AS (
SELECT…
novog
- 121
- 11
0
votes
1 answer
sql server using recrusive cte to get the level in the same group
I have a sql server table showing the IDs and their previous IDs,
create table test2 ( ID varchar(10) ,
Pre_ID varchar(10)
)
insert into test2 values ('e','d')
, ('d','c')
,…
Tony Jing
- 3
- 1