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
3
votes
2 answers
CTE Recursion Ordered Tree
I have create this SQL Fiddle with the following data
userId userName managerId
====== ======== =========
1 Adam NULL
2 Brett 1
3 Chris 2
4 George 1
5 David 3
6 …
openshac
- 4,966
- 5
- 46
- 77
3
votes
1 answer
Coalesce overlapping time ranges in PostgreSQL
I have a PostgreSQL (9.4) table that contains time stamp ranges and user IDs, and I need to collapse any overlapping ranges (with the same user ID) into a single record.
I've tried a complicated set of CTEs to accomplish this, but there are some…
Ben Wyatt
- 399
- 4
- 14
3
votes
2 answers
Select statement to return parent and infinite children
Give the table structure, as something like:
ID ParentID Name
1 NULL A root
2 NULL Another root
3 1 Child of 1
4 3 Grandchild of 1
5 4 Great…
Darren Wainwright
- 30,247
- 21
- 76
- 127
3
votes
1 answer
Issue with recursive CTE in PostgreSQL
This query generates the numbers from 1 to 4.
with recursive z(q) as (
select 1
union all
select q + 1 from z where q < 4
)
select * from z;
But, if I modify it to this,
with x as (
select 1 y
),
recursive z(q) as (
select y from x
…
Noel
- 10,152
- 30
- 45
- 67
3
votes
3 answers
get last node given the full path of all ancestor's node attributes using cte
Given the following PostgreSQL table:
items
integer id
integer parent_id
string name
unique key on [parent_id, name]
parent_id is null for all root nodes
Currently I build the sql query manually, doing a join for every path element. But is…
gucki
- 4,582
- 7
- 44
- 56
3
votes
1 answer
Numeric Overflow in Recursive Query : Teradata
I'm new to teradata. I want to insert numbers 1 to 1000 into the table test_seq, which is created as below.
create table test_seq(
seq_id integer
);
After searching on this site, I came up with recusrive query to insert the numbers.
insert into…
Noel
- 10,152
- 30
- 45
- 67
3
votes
4 answers
PostgreSQL pass data from recursive CTE onto function
I have the following problem: I am trying to discover all possible paths from source node (node_s) to target node (node_t).
The format of the original table with graph edges is simple: | node_x | node_y | strength | , where "node_x" -> "node_y" is …
aza07
- 239
- 1
- 6
- 14
2
votes
2 answers
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 functions.
I have provided a temp table below that…
DizzleBeans
- 173
- 6
2
votes
1 answer
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 fed into the string_agg(column_name,',')…
Coldchain9
- 1,373
- 11
- 31
2
votes
1 answer
use recursive common table expressions like a while loop
i have the following table
Log
Date date
Description varchar
ID integer
Given a date as a parameter,i have to find the no. or count of logs logged each day from start date to a month after it using recursive…
psy
- 914
- 3
- 10
- 31
2
votes
1 answer
In a list of Product and Year, use a Recursive Function to Include products of previous years
DB-Fiddle
This is a simplification of the problem I'm facing. It boils down to a CTE that shows a list of products of the current and previous years. For the first year (2018), it will only show 2018 products. For the second year (2019), it will…
Peter PitLock
- 1,823
- 7
- 34
- 71
2
votes
0 answers
PonyORM: using WITH RECURSIVE CTE clause without writing raw SQL (recursive query)
Is it possible to write a query in PonyORM's syntax that will be translated into a SQL query with a WITH RECURSIVE CTE clause? If yes, how?
I know I can write raw SQL queries with select_by_sql() and select(), but I specifically want to know if I…
idoric
- 21
- 1
2
votes
1 answer
Recursive CTE while Parent Id not in a List
I have the following Nested Set
That results in this tree
1 -
|---- 2 -
| |---- 4 -
| |---- 7
| |---- 8
|----10 -
|---- 9
3 -
|---- 5
|---- 6
13-
|---- 11
|---- 12
I…
LoadIt
- 137
- 1
- 1
- 9
2
votes
3 answers
How to use recursive CTE to add resolution to a data set
I'm attempting to create a recursive CTE statement that adds blank rows in between data points that will later for interpolation. I'm a beginner with SQL and this is my first time using CTE's and am having some difficulty finding the proper way to…
Lyric
- 43
- 1
- 5
2
votes
1 answer
How to get time from 00 to 23 using recursive CTE?
How do I get hours of a day from 00 hrs to 23 hrs using recursive CTE?
It gives hours 00 to 24, but I need to exclude the 24 hrs in my result set or in other words I need only up to 00 to 23 hrs
My Code:
DECLARE @calenderDate DATETIME2(0) =…
Mar1009
- 721
- 1
- 11
- 27