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
Oracle SQL to Mimic Nested Window Function
My actual problem involves a larger row source and more involved math, but this is a small example that still exhibits the challenge faced. Using Oracle 19c.
Suppose we have a table X with four rows of data as follows.
x
-
1
2
3
4
Further, suppose…
Alex Bartsmon
- 471
- 4
- 9
0
votes
1 answer
Is it possible to utilize Recursive CTE in this example?
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable
create table #MyTable (PolNo varchar(50), ControlNo int, PrevControlNo int, Premim money)
insert into #MyTable values ('Policy-00', 5000, NULL, 1000),
…
Serdia
- 4,242
- 22
- 86
- 159
0
votes
1 answer
Is there a way to create a groupID for a recursive CTE in SSMS?
I'm building a query that outputs an ownership hierarchy for each root in my database. I'm using a recursive CTE with success in that I can achieve the following data output currently:
rootID RootName RelatedName
1 ABA GPS
1 ABA PIG
1 ABA…
SQALEX101
- 209
- 1
- 3
- 16
0
votes
1 answer
Recursive CTE sql query for calculating Rolling returns
My first time writing a recursive CTE in SQL to calculate the rolling returns.
Formula : 100*(1+Returns) for first value, and then (RollingReturns)*(1+Returns)
The table is as below:
+----+--------+--------+----------------+
| ID | Date | Return |…
Dipesh Surana
- 93
- 2
- 7
0
votes
1 answer
How to rewrite CONNECT BY PRIOR Oracle style query to RECURSIVE CTE Postgres for query with correlated WHERE clause?
Now I have following working query for Oracle:
select * from (
select orgId, oNdId, stamp, op,
lgin, qwe, rty,
tusid, tnid, teid,
thid, tehid, trid,
name1, name2,
…
gstackoverflow
- 36,709
- 117
- 359
- 710
0
votes
0 answers
Partitioning row number by variable time window in Hive SQL
This is not the easiest to explain, so I will do my best, but please ask if you need more clarification.
I'm using hive SQL, and have a table containing user and event time. What I'm looking to calculate, as shown in the rownum column below, is an…
Rossy
- 65
- 1
- 6
0
votes
1 answer
Insert inside recursive CTE in SQL Server
I have a table that has a parent-child record relationship across two fields. I wrote a query with a recursive cte that returns all child records to a particular one. Now I need to insert them to another table where the parent-child relation is set…
Igor Chemidov
- 31
- 1
- 1
- 6
0
votes
1 answer
Looping and testing the result of a cte in a PostgreSQL trigger
PostgreSQL 11.1
I am using the following trigger to obtain a "new" chart number from the chart_gap table:
CREATE FUNCTION phoenix.next_chart()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
--…
Alan Wayne
- 5,122
- 10
- 52
- 95
0
votes
2 answers
Recursive CTE using inner join in SQL
I have to make an Recursive CTE to give me back all the actors who knew kevin bacon, to get better idea about my database I have Movies table and Actors and Movies2Actors table that contains only the actorid and the movieid, I need to get only the…
maha
- 11
- 2
0
votes
1 answer
How does one Create a Parameterized Recursive CTE to flatten a heirarchy within a Scalar Function?
I'm trying to create a scalar function to determine whether a user of a provided ID or any of their subordinates have orders under a collection of provided order IDs.
Note I am using my own User-Defined Table Type of IntegerIdTableType to take in…
Matt Arnold
- 668
- 2
- 8
- 21
0
votes
1 answer
Calculate position change using the previous rows row_number value
I'm trying to calculate the positional change based on the previous rows ROW_NUMBER() compared to the current ROW_NUMBER()
My query is using a recursive cte with a ROW_NUMBER() OVER .. clause which neatly gives me the row number of the result by its…
Jason246
- 580
- 5
- 7
0
votes
1 answer
How to diagnose slow/inconsistent SQL Server query?
Running Windows Server 2012, Hyper-V, SQL Server 2012 Active/Passive failover cluster w/two 8-processor, 60GB nodes, single instance, 300 databases. This query produces inconsistent results, running anywhere between 10 and 30 seconds.
DECLARE @OrgID…
sthames42
- 888
- 8
- 19
0
votes
1 answer
Reverse SELECT query result
I have recursive query which returns some rows from hierarchical model.
Kind Of:
files_array := ARRAY
(WITH RECURSIVE files_to_parent AS (
SELECT FileID, Name, ParentID
FROM File
WHERE FileID = file_id
UNION…
Serhiy
- 1,332
- 1
- 16
- 24
0
votes
2 answers
Recurcive cte to identify circular reference in data
I am trying to identify recursive/circular reference in my data for which I need recursive cte.
For example I have table that contains Product_ID and Inner_Product_ID. I want results when Product_ID A is inner to Product_ID B, which is inner to…
Aura
- 1,283
- 2
- 16
- 30
0
votes
0 answers
Use CTE To Create Subset of Data
I'm wanting to create a subset of data for testing purposes, where I start with a Users table, select X of type a, Y of type b, Z of type c (and so on), and create a new table containing those users. I then need to to create all related tables (with…
Jake
- 893
- 2
- 9
- 17