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
2
votes
3 answers

WHERE Column NOT LIKE isn't working correctly with recursive cte

This is SQL Server code. Let's say you have a table with three columns. Column 1 is named Monster, Column 2 is named Level, and Column 3 is named BodyType. Level signifies how powerful the monster is and BodyType signifies what type of body it…
Kian
  • 389
  • 1
  • 2
  • 15
2
votes
1 answer

Modify SQLite query for CTE

Here's my query: WITH desc_table(counter, hourly, current_weather_description, current_icons, time_stamp) AS ( Select count(*) AS counter, CASE WHEN strftime('%M', 'now') < '30' THEN strftime('%H', 'now') …
M. Smith
  • 379
  • 1
  • 20
2
votes
1 answer

Postgres: Best way to query hierarchy structures by name

Suppose I have a hierarchy of categories as follows: id | name | parent_id ---+------------+----------- 1 | Computers | ---+------------+----------- 2 | Laptops | 1 ---+------------+----------- 3 | Desktops |…
Jake
  • 15,007
  • 22
  • 70
  • 86
2
votes
2 answers

SQL Server: Select Parent-Child

I have SQL Server 2008 with a table called ProductCategories designed like this: Id | Name | ParentId 71 PCs NULL 32 MACs NULL 3 Keyboard 1 9 Mouse 1 5 Screen 1 11 Keyboard 2 7 Mouse 2 8 …
MartinHN
  • 19,542
  • 19
  • 89
  • 131
1
vote
1 answer

Having a single recursive CTE while there are multiple CTEs in a query

I am not sure if this has been asked before but I have searched across different forums and haven't found the exact answer. Should we always initialize the word RECURSIVE to the first CTE (even though it's not the recursive CTE) for the entire query…
Stryken_03
  • 23
  • 1
  • 7
1
vote
1 answer

How to query leaf nodes using a recursive CTE in DolphinDB？

I want to traverse a tree-like data structure and query the leaf nodes. I have already written an SQL query to accomplish this task using the WITH RECURSIVE syntax. However, I am now working with DolphinDB and need to write a similar script using…
yiniwei
  • 89
  • 5
1
vote
2 answers

How to pull hierarchical parents till the root parent without traversing redundant paths?

I have a recursive SQL query that traverses a tree-like structure using a table variable. The query works correctly, but I'm facing performance issues. The idea to pull hierarchical parents till the root parent without traversing redundant…
minato namikaze
  • 29
  • 1
  • 6
1
vote
1 answer

MySQL validating parents by children

I am programming in Visual Studio, but I need to perform validation on mySQL (v8.0.28) table and I guess it would be faster if it will be performed as procedure on server. Unfortunatelly, my MySQL programming skills are very limited. This is…
Tomas Novak
  • 13
  • 3
1
vote
1 answer

Crafting a Subquery-able UNION ALL based on the results of a query

Data I have a couple of tables like so: CREATE TABLE cycles ( `cycle` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `cycle_type` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `start` date DEFAULT NULL, `end` date DEFAULT…
jobu1342
  • 150
  • 7
1
vote
1 answer

CTE performance issue when add one more field on selection list

I have a table with hierarchical data (contains ~5.000.000 records). I use the following query to retrieve all children records of a specific record: with TEMP_PACKAGES as ( select ID AS PACKAGE_ID,PARENT_PACKAGE_ID,1 as LEVEL from PACKAGES…
Dimitris Stoufis
  • 11
  • 2
1
vote
1 answer

Infinity loop in recursive sql query

Maybe someone will advise me on how to solve my problem. I have no idea why it happens and how to solve it. In my opinion the reason that my sql code is not working is that it becomes to infinitive loop. I have table: CREATE TABLE…
Vilius Gaidelis
  • 430
  • 5
  • 14
1
vote
2 answers

SQLite from android get path like indexes with recursive query

I have this table named items with following columns and rows. id item_name parentId -------------------------- 1 Item 1 0 2 Item 2 1 3 Item 3 2 4 Item 4 1 5 Item 5 2 6 Item 6 3 7…
alexandre9865
  • 493
  • 2
  • 9
  • 24
1
vote
1 answer

PostgreSQL Time Dimension (By Hours and Days) Error

I am am building a Time Dimension table in PostgreSQL with DATE_ID and DATE_DESC. My T-SQL (works perfectly) script is: set DATEFIRST 1 ;WITH DATES AS ( SELECT CAST('2019-01-01 00:00:00.000' AS datetime) AS [DATE] UNION ALL SELECT…
Konstantins Kovalovs
  • 25
  • 4
1
vote
2 answers

Recursive Query in Azure Synapse Analytics for Dates

In the table below, how to insert rows with the first and last date of years between the START_DATE and END_DATE column? EMPID EMPNAME START_DATE END_DATE 1001 Shivansh 2015-09-01 2018-03-31 1004 Mayank 2019-04-01 2020-06-30 The output…
Abhishek Dutta
  • 75
  • 4
  • 12
1
vote
1 answer

Fill missing rows using RECURSIVE CTE in SQL SERVER

Input id, date, value 1, '2020-01-01', 100 1, '2020-01-03', 200 1, '2020-01-05', 500 Output expected 1, '2020-01-01', 100 1, '2020-01-02', 100 1, '2020-01-03', 200 1, '2020-01-04', 200 1, '2020-01-05', 500 I do not want to use any…
Anantha Subramanian
  • 13
  • 3
1 2
3
…
8 9