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
113
votes
4 answers
How to use multiple CTEs in a single SQL query?
Is it possible to combine multiple CTEs in single query?
I am looking for way to get result like this:
WITH cte1 AS (
...
),
WITH RECURSIVE cte2 AS (
...
),
WITH cte3 AS (
...
)
SELECT ... FROM cte3 WHERE ...
As you can see, I have one recursive…
axvm
- 1,876
- 2
- 11
- 19
29
votes
3 answers
Get all parents for a child
I want to retrieve the parentid of an id, if that parentid has a parent again retrieve it, and so on.
Kind of hierarchy table.
id----parentid
1-----1
5-----1
47894--5
47897--47894
am new to sql server and tried, some queries like:
with name_tree as…
srinioracle
- 301
- 1
- 3
- 11
20
votes
6 answers
To find infinite recursive loop in CTE
I'm not a SQL expert, but if anybody can help me.
I use a recursive CTE to get the values as below.
Child1 --> Parent 1
Parent1 --> Parent 2
Parent2 --> NULL
If data population has gone wrong, then I'll have something like below, because of which…
Interstellar
- 662
- 2
- 10
- 29
12
votes
1 answer
Why do Recursive CTEs run analytic functions (ROW_NUMBER) procedurally?
I answered a recursive CTE yesterday that exposed an issue with the way that these are implemented in SQL Server (possibly in other RDBMS, too?). Basically, when I try to use ROW_NUMBER against the current recursive level, it runs against each row…
Justin Pihony
- 66,056
- 18
- 147
- 180
12
votes
3 answers
Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?
Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?
If not, then how can we create a column similar to "LEVEL"?
Neha
- 201
- 1
- 4
- 10
10
votes
2 answers
Using recursive CTE with Ecto
How would I go about using the result of a recursive CTE in a query I plan to run with Ecto? For example let's say I have a table, nodes, structured as so:
-- nodes table example --
id parent_id
1 NULL
2 1
3 1
4 1
5 2
6 2
7 3
8 …
Saba
- 401
- 5
- 13
6
votes
1 answer
Complex recursive SQL to produce hierarchical data
I am trying to evaluate the impact of store visitors on the spread of COVID-19.
Here is a simple scenario:
VisitorA walks into store and meets Employee1 @ Time = 0.
VisitorA then meets Employee2 @ Time = 1.
VisitorB walks into store and meets…
nael
- 1,441
- 19
- 36
6
votes
5 answers
Repeat value between two values in a column
I'm trying to repeat the first specific non-empty value in a column till the next specific non-empty value in the same column. How do I do that?
The data looks like this:
ID | Msg
---+-----
1 |
2 |
3 |
4 |
5 | Beg
6 | End
7…
SQLserving
- 380
- 1
- 4
- 16
6
votes
2 answers
SQL Server CTE loop; insert all record together
I have this situation:
drop table #t1;
drop table #t2
select *
into #t1
from
(select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select…
elle0087
- 840
- 9
- 23
6
votes
4 answers
Recursive CTE Problem
I am trying to use a recursive CTE in SQL Server to build up a predicate formula from a table containing the underlying tree structure.
For example, my table looks like:
Id | Operator/Val | ParentId
--------------------------
1 | 'OR' …
Chris
- 183
- 1
- 10
4
votes
1 answer
Does this require recursive CTE, just creative window functions, a loop?
I cannot for the life of me figure out how to get a weighted ranking for scores across X categories. For example, the student needs to answer 10 questions across 3 categories (both # of questions and # of categories will be variable eventually). To…
cporichie
- 93
- 5
4
votes
1 answer
Recursive CTE-Find all Employees Below Manager
I created a sample fiddle for this SQLFIDDLE
CREATE TABLE [dbo].[Users](
[userId] [int] ,
[userName] [varchar](50) ,
[managerId] [int] ,
)
INSERT INTO dbo.Users
([userId], [userName],…
vmb
- 2,878
- 15
- 60
- 90
3
votes
3 answers
Group unique users with two changing IDs
Can you think of a faster algorithm for this problem? Or improve the code?
Problem:
I have two customer IDs:
ID1 (e.g. phone number)
ID2 (e.g. email address)
A user sometimes change their ID1 and sometimes ID2. How can
I find unique…
thenarfer
- 405
- 2
- 14
3
votes
1 answer
Recursive JSONB postgres
I am trying to build a recursive CTE in Postgres that supports both arrays and objects, to return a list of key-value pairs and don't seem to be able to find a good example. This is my current code.
with recursive jsonRecurse as
(
select
j.key as…
Simon Tulett
- 149
- 1
- 9
3
votes
1 answer
Order child items in a certain order in a multi-level hierarchy query in SQL Server 2012
I have a table in SQL Server 2012 called Items which contains different items that are related to each other in a parent-child relationship through ParentId column. This table contains a topmost level item with ItemId of 429965 and its children,…
Sunil
- 20,653
- 28
- 112
- 197