A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [recursive]

Using recursion to solve database problems.

Filter by
Sorted by
Tagged with
2
votes
1answer
12k views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
6
votes
2answers
2k views

Improve performance for order by with columns from many tables

Using PostgreSQL 8.4, I'm trying to consult two tables with 1 million records using order by with indexed columns of the two tables, and I'm losing performance (with 1 column takes 30 ms and with two ...
33
votes
2answers
9k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
17
votes
5answers
7k views

How to recursively find gaps where 90 days passed, between rows

This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this. SELECT ...
7
votes
1answer
1k views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
12
votes
1answer
9k views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
16
votes
2answers
8k views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
0
votes
4answers
3k views

Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?

Example you have the following table CREATE TABLE emp (id,name,salary,manager) AS VALUES ( 1 , 'james' , 10000 , null ), ( 2 , 'alex' , 5000 , 1 ), ( 3 , 'Alice' , 4500 , 1 ), ...
3
votes
1answer
581 views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
1
vote
1answer
2k views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...