Questions tagged [recursive-query]

The ability to run recursive queries in SQL

474 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
31votes
3answers
3kviews

Oracle SYS_CONNECT_BY_PATH equivalent query into SQL Server

I'm trying to convert a complex query involving the Oracle SYS_CONNECT_BY_PATH syntax into SQL Server: SELECT DISTINCT TO_CHAR(CONCAT(@ROOT, SYS_CONNECT_BY_PATH(CONCAT('C_',X), '.'))) ...
user avatar
  • 1,347
5votes
1answer
373views

Applying CTE for recursive queries

I am trying to apply CTE and recursive queries. The database is MariaDB 10.2 or greater. Business rules are as follows: An account can either be a holding or a portfolio. A holding consists of a ...
user avatar
  • 22.4k
5votes
0answers
1kviews

MySQL Recursive query to find the shortest path

I have an issue I just can't get my head around... I have a table called country_neighbour looking like this. Country_name Country_id Neighbour_name Neighbour_id Italy 1 France ...
user avatar
  • 51
4votes
1answer
175views

Recursively count occurrences with sqlite

Consider the following example table x_id name_id1 name_id2 x1 John Frank x2 Frank John x3 Jack John x4 John Jack x5 Bob Frank x6 George Bob x7 Bob Finn x8 Mark James x9 James Finn The goal ...
user avatar
4votes
0answers
5kviews

SQLAlchemy simple recursive cte query

I have the following SQLAlchemy table: from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class NetworkLink(Base): """Network immediate link between a franchisee ...
user avatar
  • 133
4votes
1answer
247views

F# recursive types to SQL tables

I'm modeling an application in F# and I encountered a difficulty when trying to construct the database tables for the following recursive type : type Base = | Concrete1 of Concrete1 | Concrete2 ...
user avatar
  • 313
4votes
1answer
978views

Recursive Loop - Parent/Child Tree

I'm trying to recursively loop through and return all child_id's that have the root element of 9. The structure: +-- 9 | +-- 8 | +-- 17 | +-- 33 | +-- 18 | +-- 22 | ...
user avatar
4votes
1answer
868views

Recursive CTE to update parent records through multiple levels

Will a CTE use data that is updated as part of the CTE in the next recursion? I am trying to attempt this CTE because the performance of a similar UPDATE logic inside of a WHILE loop is not performing ...
user avatar
3votes
0answers
62views

SQL problem: Is there way to order rows within a partition and then carry forward a column that self-references?

Specifically looking for SQL Server (or ideally ANSI SQL) solutions. I wrote a recursive CTE solution for this but it does not perform remotely well for larger data sets. First, I'll set the stage for ...
user avatar
  • 91
3votes
1answer
617views

Use of LIMIT in Recursive Common Table Expression is not allowed in MySQL

My goal is to construct a tree using newest MySQL's WITH RECURSIVE method. My table is called categories which has 2 rows. The ID and the parentID row. My Categories Table: . ID . | ParentID ---...
user avatar
3votes
1answer
71views

Recursive Query ordering by find order

I am building a query, in which i need the results to be returned in the order that the query found the values. HERE IS THE QUERY DECLARE @Query TABLE ( [ParentID] VARCHAR(50) NULL, [ChildID]...
user avatar
3votes
0answers
2kviews

Spark SQL - Recursive reading in folder

I try to use HiveContext to take advantage of some windows functions in HiveQL in SparkSQL. But it cannot help me to read recursively data file in a folder (partition folder by year and month). My ...
user avatar
3votes
1answer
736views

Recursive CTE causes slowness and index scan

I have a table (Location_Tree) containing location information, arranged in a Id/ParentId structure on several levels, from level 0 of "World" right down to individual cities, via worldregions, ...
user avatar
3votes
1answer
3kviews

How to use a CTE as a loop?

Is there any way to convert this cursor into a looping/recursive CTE? I've been reading some of the articles on CTEs but they've been about hierarchies and they made my head spin. create table #...
user avatar
3votes
0answers
747views

PostgreSQL recursive CTE million size tree select by dynamic rank with paging

My goal is to create a query where I can retrieve a tree by controlling the depth and amount of items returned at each depth sorted by rank descending, which the rank is frequently changing. I.e. ...
user avatar
3votes
1answer
1kviews

How to determine Strahler number on a directed graph for a stream network

Question / example / expected values I need to determine a Strahler number or Strahler stream order for a directed graph representing a stream network. I can derive information forwards and backwards ...
user avatar
  • 37.4k
3votes
4answers
235views

How can i get records of id those are connected in multiple row value

Following is my table Id User_id type content item_id secondary_item_id 879 1 activity_comment Amazing 833 833 907 168 ...
user avatar
  • 2,043
3votes
1answer
1kviews

MySQL - Recursive query with recursive concatenation

My table structure: id | name | parent_id 0 | "name0" | NULL 1 | "name1" | 0 2 | "name2" | 1 ... I need something like: id | full_path 2 | "name0|name1|name2" 2 is specified by user;...
user avatar
2votes
0answers
24views

How to improve recursive query performance in MongoDB?

MongoDB has been a really great fit for an application I'm trying to build, except for potentially one area which I am REALLY hoping there's a solution to. The majority of the data will be a forest. ...
user avatar
2votes
3answers
81views

PostgreSQL recursive query for calculation parent value

Here is entry's hierarchy. _________Milky Way (30)________ / | \ Alpha(10) Beta(20) Delta(null) / \ ...
user avatar
2votes
1answer
470views

Alternative of HIERARCHY_ANCESTORS function in SAP HANA

I wrote a piece of code in HANA and used the HIERARCHY_ANCESTORS function. However, it started giving me all sorts of OOM (Out of Memory) issues. I raised an OSS & provided all the details just to ...
user avatar
2votes
3answers
313views

BigQuery missing rows with SUM OVER PARTITION BY

TL;DR: Given this table: WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, "premium" as product, 50 as diff UNION ALL SELECT TIMESTAMP("2020-11-01"), "...
user avatar
2votes
1answer
65views

How to create a cluster of related entries in a many-to-many-relation?

I have a table of subscription users, with a contact ID and order ID. Multiple contacts can be linked to one order and a contact can be linked to multiple orders. I'm trying to take a given order, ...
user avatar
2votes
1answer
36views

Pivot status of booking by date

A package can be booked between 2 dates, and the status of the booking gets updated by 2 users (0 as unconfirmed, 1 as pending confirmation, 2 as confirmed). I am trying to display the status of a ...
user avatar
2votes
0answers
72views

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 ...
user avatar
  • 21
2votes
1answer
33views

TSQL retreive the next item if item has already been assigned

I have a sample data set which looks like: I need to return the next value if the value hasn't already been taken. I would like to achieve this by not using a cursor. The data which should be ...
user avatar
2votes
0answers
104views

CTE in recursive CTE to access recursive part multiple times

I have the following query that runs fine under postgres and I hope to make it work on MySQL as well: WITH RECURSIVE dependent(id) AS ( SELECT id FROM data_category WHERE id = :cat UNION ( ...
user avatar
  • 1,641
2votes
1answer
59views

SQL cross match IDs to create new cross-platform ID -> how to optimize

I have a Redshift table with two columns which shows which ID's are connected, that is, belonging to the same person. I would like to make a mapping (extra column) with a unique person ID using SQL. ...
user avatar
  • 21
2votes
1answer
46views

Need Help on Sql recursive query

I need to simulate end date/time for a production order. Ex:the production order take 4:55 hours but it may have 1 or 2 break and maybe 1 lunch time depend hours needed and current time. I need to ...
user avatar
2votes
1answer
48views

Count people in companies in tree structure

Hello I have a structure table with companies_id(PK) and parent_companies_id(FK) column and people table with companies_id (FK) and people_id(PK). Some people can be in different companies. I want to ...
user avatar
  • 693
2votes
0answers
553views

"ORA-00932: inconsistent datatypes: expected NUMBER got -" but why?

I have the a table example1 with 2 datetime columns, these columns does not necessarily have all days (i.e. might not include weekends). I want to create a sequence of days starting from a year ago ...
user avatar
  • 171
2votes
1answer
278views

Optimization of Memory while reading a large excel sheet

I do have an excel file of which i developed a way of reading all the contents in sheet 1 and populates it into a listview, basically the sheet has data in cells A-D. but the number is 120,000. when i ...
user avatar
  • 85
2votes
0answers
138views

Generate WORKFLOW in plSQL

i try to generate a workflow with recusive functions in plSQL but i don't get what i want. i got this table TABLETEST : GROUPE STATUS STATUSNEW A A1 A2 A A1 A3 A ...
user avatar
2votes
0answers
106views

Find possible paths with a recursive query

I've read this useful article related to SQL recursive queries. I want to do the same thing in Android and with SQLite. Here is the original query: WITH RECURSIVE search_path (path_ids, length, ...
user avatar
  • 400
2votes
1answer
40views

Recursive Sql Query 2008

I need to calculate the value of indebtedness column so that when openingBalance !=0 then indebtedness = openingBalnce+SalesTotal-SalesReturn. But, when openingBalnce = 0 then indebtedness = ...
user avatar
2votes
0answers
308views

Recursive CTE query works on SQLiteSpy (not always) and fails on Python

The following code creates a database with a few records, then executes a recursive query, then executes the same recursive query with a UNION operator. If I run the code on Python 3.4.0 with sqlite ...
user avatar
  • 7,669
2votes
1answer
2kviews

Recursive SQL: aggregate function using Recursive Subquery Factoring

Table T represents a tree. Every record is a node and each node has only one parent. This query calculates the SUM() of each branch for every node. WITH t AS (SELECT 1 id, NULL parent_id,...
user avatar
  • 537
2votes
2answers
904views

Exploding BOM through recursive CTE

I'm trying to explode the BOM through recursive CTE, but I'm still getting the anchor rows only. However, I'm trying to use this SQL code: With BOMTree_CTE AS ( --Anchor Select B.BOMID ,B....
user avatar
  • 31
2votes
0answers
513views

Mysql : Get articles from multi level category?

Assume that I have a article_category tree like this: GrandPa Cate |______ Parent Cate 1 (article 1, article2) |______ Parent Cate 2 |______|_____ Child Cate 1 (article 3, article 4) |______|_____ ...
user avatar
  • 1,269
2votes
1answer
544views

Add column with cte containing parent of one level up

I have the following code in a View that works well and gives the required output. ;WITH ctesample AS (SELECT sjob, mjob, mjob AS DisplayMJob, ...
user avatar
2votes
1answer
96views

What's the most elegant way to do this recursive many-to-many database lookup?

I have a MySQL table which contains many-to-many data about users: username (PK) ip (PK) machine_id (PK) -------------------------------------------------- tester1 1.2.3.4 ...
user avatar
  • 2,335
2votes
1answer
2kviews

SQL computed column, recursively summarizing a hierarchy

The [simplified] story so far: In a .mdf DB under Visual Studio 2010, I have a the following table: CREATE TABLE [dbo].[SandTable]( [id] [int] IDENTITY(1,1) NOT NULL, [isDone] [bit] NOT NULL,...
user avatar
2votes
1answer
197views

Recursive SQL with more tables

I've a question about the use of recursive SQL in very particular situation which I describe below. I have a table A with a (ID, value) schema and these sample rows: (1,1000) (2,800) (3,300) (4,600) ...
user avatar
2votes
0answers
280views

NSFetchRequest with recursive criteria or recursive Fetched Property

In a sqlite based core data storage I have 2 entities: Item and Tag. Item has a many to many relationship to Tag. Tag also has a recursive relationship for one parent tag. Hence tags form a tree. ...
user avatar
  • 21
2votes
1answer
88views

I need sql query for getting the Required format

i am having table Category with the columns like id(AutoIncrement),Parent_id,Level,Name initially for level 1 datas has Parent_id is 0. autoincrement id will be Parent_id for next levels .my table ...
user avatar
2votes
1answer
338views

MySQL: standard approach VS set-based approach for multi-row results

Following up on this question, there is problem with the standard approach or a recursive approach in the case below. For instance, I want to return the page with the same parent_id and some pages ...
user avatar
  • 50.6k
2votes
2answers
273views

Recursively Distribute Rows in Sql server

I have problem to device a query which will be used to obtain information from existing rows for other rows. Existing table data In above image there are three rows for RecptNo 20100001182. In ...
user avatar
2votes
1answer
151views

PostgreSQL: vocabulary overview in json, window functions... recursion?

I have a "dictionary" into PostgreSQL 9.3. I want to get all terms, splitting them by triples (the first three characters) between pages with up to 30 terms per page. So, no any triple should be ...
user avatar
  • 427
1vote
1answer
53views

How to perform recursive query in spanner

I have the following table structure in spanner, I would like to perform a recursive query as shown below in spanner. Can anyone please help to perform the below query. It seems Recursive queries are ...
user avatar
1vote
0answers
29views

Karatsuba algorithm splitting number in 3 strings

Im trying to code the Karatsuba algorithm with some changes. Instead of splitting each number into 2 strings, I want to split it into 3. For example: Common Karatsuba -> first number would goes ...
user avatar
  • 11

15 30 50 per page
1
2 3 4 5
10