Stack Overflow
Stack Overflow

Questions tagged [recursive-cte]

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.

133 questions
1
vote
3 answers

Linked lists: query first and last element of chained lists stored in SQL table

I have an SQL table with "lines" representing elements of chained lists. I could for example have the following records: (id, previous_id) ------------------ (1, NULL) (2, NULL) (3, 2) (4, 3) (5, NULL) (6, 4) (7, 5) We have 3 lists in this…
Charles B
  • 160
  • 1
  • 12
1
vote
3 answers

Recursive CTE Alternative for Concatenating Strings in Redshift

I have a table where there are 4 columns namely :Book_no,Prev_Book_no(previous edition book_no),Edition_no(starts with 0 upto latest edition no(increments by 1)), Tree(Initially null). What I intend to do is to create a tree of Book_no starting from…
NewCoder
  • 31
  • 1
  • 5
1
vote
0 answers

Is there a faster way than LIKE to RECURSIVELY join in SQL-Server, without cycles?

I need to recursively JOIN a large graph that can have cycles. Now, a dumbed down version of that looks like this in SQL-Server: -- Array:10'000: 1.6s (postgresql) -- LIKE: 10'000: 19s (sql-server) -- JSON: 10'000: 19s (sql-server) -- XML :…
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
1
vote
0 answers

Recursive CTE across many-to-many tables

I'm working a a recursive CTE to trace all relationships in a DB, using foreign keys, in order to create some test data. Everything is working well, up until the point that I hit a table that contains many-to-many relationships. I've taken a look at…
Jake
  • 893
  • 2
  • 9
  • 17
1
vote
0 answers

How to ignore a syntax error in MySQL workbench?

I have created an EER Diagram in MySQL workbench 6.3.10-4. Now I need to add a stored procedure that uses MariaDB 10.2.2 functionality (which has also been released in MySQL 8.0), namely recursive CTE's. However, the syntax checker of MySQL…
Simon Baars
  • 1,877
  • 21
  • 38
1
vote
1 answer

Postgres directed graph traversal up AND down

I've got a problem which I can solve for small data sets but fails on large ones with (possibly) unclean data. The database is an implementation of an acyclic (hopefully) graph in PostgreSQL. With three tables vertex_elements: id edges: id,…
fpcyan
  • 11
  • 1
  • 3
1
vote
0 answers

Recursive CTE in hive

I have a recursive CTE written in SQL server which i need to migrate to HIVE. As hive does not support recursion in CTE, i am stuck here. i am advised to write a java UDF for this but i am not expert in java. If anyone could help here, i would be…
Swapnil Pal
  • 21
  • 6
1
vote
2 answers

query parents in a tree

I have database table Task as below. SELECT _id,name,parentId FROM Task; _id name parentId ---------- -------------------- ---------- 4 Software Development 5 Machine Learning …
Kevan
  • 1,085
  • 1
  • 9
  • 15
1
vote
1 answer

Recursive CTE SQL (Parent Child)

I'm using SQL Server 2012 LOCATIONDETAIL table OID LOCATIONNAME PARENTID 1 GLOBAL 0 2 NORTH 1 3 SOUTH 1 4 NORTH1 2 5 SOUTH1 …
Marid
  • 83
  • 10
1
vote
2 answers

SQL Recursive CTE: Finding objects linked by property

I'm just trying to understand CTE and recursion to solve an issue that I would previously have used a cursor for. create table ##ACC ( AccNo int, Property char ) Insert into ##ACC VALUES (1,'A'),(1,'B'),(2,'A'),(2,'C'),(3,'C'),(4,'D') What I'm…
GavinP
  • 677
  • 1
  • 5
  • 18
1
vote
1 answer

CTE Create product hierarchy tree

I have the following three tables representing product data. To briefly explain, products "A" and "B" are end products. To find out the parts that make product "A", we look at the "ProductComponents" table giving us the ComponentListId = 1. Querying…
Syed
  • 340
  • 1
  • 3
  • 13
1
vote
1 answer

Find full path of a file in a file system

I have a file system like this: C Drive - Docements - (empty) - Music - Rock - a.mp3 - Jazz - SmoothJazz - b.mp3 - Photo - (empty) D Drive - (empty) Every file or directory has an id, and its parent's id. Now given a…
Dylan Czenski
  • 1,305
  • 4
  • 29
  • 49
1
vote
1 answer

SQL replacement for Recursive CTE

I have a table Test which contains TEST ---- tablename|columnvalue|rankofcolumn A|C1|1 A|C2|2 A|C3|3 A|C4|4 B|CX1|1 B|CX2|2 C|CY1|1 C|CY2|2 C|CY3|3 I want to generate the path along with other columns as…
Srini V
  • 11,045
  • 14
  • 66
  • 89
1
vote
1 answer

recursive CTE from Ordinary CTE

I have a with clause that groups some weather data by time intervals and weather descriptions: With temp_table (counter, hour, current_Weather_description) as ( SELECT count(*) as counter, CASE WHEN strftime('%M', time_stamp) <…
M. Smith
  • 379
  • 1
  • 20
1
vote
1 answer

Find All employees reporting to manager, including managers employees

I have a table with at least three columns, EMPLID, NAME, SUPERVISOR_ID. In a single query, I'd like get a managers direct reports including any direct reports from subordinate mangers all the way down to the lowest staff when given a managersid in…
Jason R.
  • 379
  • 1
  • 6
  • 19
1 2 3
…
8 9