Stack Overflow
Stack Overflow

Questions tagged [recursive-query]

The ability to run recursive queries in SQL

Recursive SQL queries offer the ability to retrieve hierarchical information in a relational database (e.g. "adjacency list model"). The SQL standard defines recursive common table expressions (CTE) for this purpose. Some vendors such as Oracle have implemented this functionality with a proprietary syntax (CONNECT BY). Recursive CTEs are supported by a wide range of DBMS such as Oracle, SQL Server, DB2, PostgreSQL, Firebird SQL, Teradata and others.

2024 questions
4
votes
1 answer

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 of Concrete2 and Concrete1 = { Id : string …
Mario
  • 313
  • 2
  • 11
4
votes
1 answer

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 | +-- 11 | +-- 4 Parent Child Link Table:…
user7892649
4
votes
1 answer

convert postgresql query to Native Query

I want to convert the below postgresql query to hibernate query or any type of query that supports in java WITH RECURSIVE tree(parent, idElement) AS ( --Anchor member definition SELECT e.fk_course_det_id,…
BeginnerBro
  • 195
  • 2
  • 18
4
votes
1 answer

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, countries, states, counties, cantons and cities. We…
High Plains Grifter
  • 1,357
  • 1
  • 12
  • 36
4
votes
1 answer

PostgreSQL efficiently find last decendant in linear list

I currently try to retrieve the last decendet efficiently from a linked list like structure. Essentially there's a table with a data series, with certain criteria I split it up to get a list like this current_id | next_id for example 1 | 2 2 | 3 3…
rfreytag
  • 1,203
  • 11
  • 18
4
votes
3 answers

SQL: Select rows only with values separated by a given interval from other results

Let's say that I have a table that looks like this, giving me the times of various events in seconds. event_time ---------- 11 14 18 20 25 39 42 43 50 I am trying to come up with a query that will give me a set of rows from this table, where each…
avalys
  • 3,662
  • 4
  • 25
  • 22
4
votes
1 answer

How can I traverse a tree bottom-up to calculate a (weighted) average of node values in PostgreSQL?

The typical example for e.g. summing a whole tree in PostgreSQL is using WITH RECURSIVE (Common Table Expressions). However, these examples typically go from top to bottom, flatten the tree and perform an aggregate function on the whole result set.…
Mischa
  • 623
  • 5
  • 17
4
votes
1 answer

MS SQL Temporary table looping

Need help on sql looping query my table : my expecting output: I am able to get 1 line only.. declare @ID nvarchar(50) = (select EMP_ID from HRS_WORKFLOW01); CREATE TABLE #TEMP(EMP_ID NVARCHAR(200),EMP_L1 NVARCHAR(200),EMP_L2…
KyLim
  • 468
  • 1
  • 6
  • 22
4
votes
1 answer

Exiting from recursive common table expression once the result set contains some value

Given the following table: create table TreeNode ( ID int not null primary key, ParentID int null foreign key references TreeNode (ID) ) How could I write a common table expression to start at the root (WHERE ParentID IS NULL) and traverse its…
Daniel
  • 47,404
  • 11
  • 101
  • 179
4
votes
2 answers

Flatten hierarchy on self-join table

I have data in a self-join hierarchical table where Continents have many Countries have many Regions have many States have many Cities. Self-joining table structure: |-------------------------------------------------------------| | ID | Name …
BlueCucumber
  • 63
  • 1
  • 7
4
votes
1 answer

MySQL recursive procedure to delete a record

I have a table, Models that consists of these (relevant) attributes: -- ----------------------------------------------------- -- Table `someDB`.`Models` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS…
emihir0
  • 1,200
  • 3
  • 16
  • 39
4
votes
1 answer

What is the most efficient way to get the latest branch out of SQL table with specific root?

Here is what I have in my MySQL DB table: id | parent | name ---------------------- 1 | null | Root 1 3 | null | Root 3 6 | 3 | something 1 7 | 6 | something 2 9 | 1 | something 3 I would like to get the most recent branch…
sschueller
  • 533
  • 1
  • 6
  • 18
4
votes
2 answers

Preserve the order of distinct inside string_agg

My SQL function: with recursive locpais as ( select l.id, l.nome, l.tipo tid, lp.pai from loc l left join locpai lp on lp.loc = l.id where l.id = 12554 union select l.id, l.nome, l.tipo tid, lp.pai from loc l left…
Rodrigo
  • 4,706
  • 6
  • 51
  • 94
4
votes
2 answers

self join in update query with transitive data

I have below tables with employee and his colleagues data,i want to update bucketid in Employee table, if there is matching Colleagues with the other employee. here in this example, Employee = 101 is matching with Employee = 103 Colleague(i.e. c1)…
user3789961
  • 135
  • 3
  • 12
4
votes
3 answers

A number of connected components of a graph in SQL

I have a graph in my PostgreSQL database, for the sake of example let's define it so: CREATE TABLE nodes (node_id INTEGER); CREATE TABLE roads (road_id INTEGER, nodes INTEGER[]); INSERT INTO nodes VALUES (1), (2), (3), (4), (5); INSERT INTO roads…
Ritave
  • 1,333
  • 9
  • 25
1 2 3
…
99 100