Questions tagged [recursive-query]
The ability to run recursive queries in SQL
recursive-query
2,061
questions
0
votes
3
answers
56
views
How can I calculate this conditional monthly limit?
In May client P003 had 600 of buy_amt (sum of sales in that period) and 1000 transfer_amt. I want to calculate the remaining limit (buy_amt - transfer_amt) but starting from top to bottom and the ...
0
votes
1
answer
80
views
Get sibling and parents with recursive SQL Server query
I have this table
Id
ParentId
1
NULL
2
1
3
1
4
NULL
5
4
6
8
7
9
Each child (Id) can have only one parent.
How to retrieve parents and sibling for a given child Id, it means:
SELECT `Here the ...
- 517
1
vote
1
answer
73
views
How to rewrite Oracle sql's hierarchical query to polars?
In Oracle I have the following hierarchical sql:
with prod as (select 10 id,'1008' code from dual union all
select 11 id,'1582' code from dual union all
select 12 id,'1583' ...
- 964
0
votes
1
answer
36
views
Combine parent id and all child ids in an array in a single row using recursive cte
I have a table pr with two columns:
stg source id, parent id
125, 124
126, 125
127, 125
128, 127
what I want a final result as:
parent id, stg_source_ids
124, [125, 126, 127, 128]
as my final row ...
0
votes
1
answer
29
views
SQL assign a value from a column in table A until all the values of a column in table B are populated
I have a table box as depicted below.
box_name
---------
box1
box2
box3
box4
box5
box6
box7
box8
box9
box10
And another table color as shown below
color
------
red
blue
green
My objective is to ...
- 3,342
-1
votes
1
answer
101
views
Recursive CTE and performance issue when anchor member is a large table
I have made some edits so that the post is clearer.
I have a large table with data like below.
The goal is to be able to retrieve the original ID and location associated with the original ID given ...
- 55
1
vote
0
answers
29
views
BIG Query recursive CTE or other possible solution
I have a table with 10 million unique Product_ID. This Unique Ids are being tested by the hr and the metric has a [good : -1, neutral : 0, bad : 1], I call this column hr_status. I want to develop a ...
-1
votes
3
answers
75
views
Conditional recursive query in SQL Server
I have these 2 tables:
TABLE 1
Id
Shortname
ParentId
C1
Child1
P1
P1
Parent1
GP1
GP1
GrandParent1
NULL
C2
Child2
P2
P2
Parent2
GP1
TABLE 2
Id
Setting
GP1
5
C2
1
The logic is the following: ...
- 517
1
vote
1
answer
55
views
Oracle Connect By hierarchy query returns top level parent but child part is null
I have a CONNECT BY hierarchal query that starts with a known child part and returns the top level parent(s). It runs quickly and works great except for one thing: it returns null values for the child ...
- 11
0
votes
1
answer
46
views
Oracle help using Connect By Root to return assembly top level parent
I'm trying to use CONNECT BY ROOT to do a recursive query that returns the top-level parent of an assembly part. My query so far is only able to return the parent one level above. I created a test ...
- 11
0
votes
0
answers
15
views
Many to Many Recursive Relationship
I have a database with a single table, with a Many to Many Recursive Relatopnship.
Need to: "Associate/join" records and also be able to delete the joins.
I've know basic sql: insert, delete,...
0
votes
1
answer
33
views
Recursive count query in PostgreSQL
I have a website on which users can place comments on nodes and also comment as a reply to other comments without a depth limit. The table structure of the comments table is as follows (simplified):
...
- 67
-3
votes
1
answer
53
views
For each employee need to get the sum of values based on their start date every month
I have two tables.
Table 1:
emp
startdate
010
15/02/2022
011
21/05/2022
012
04/012/2022
I need to have additional columns :startdate1 and enddate
which should look like:
emp
startdate
startdate1
...
- 11
0
votes
1
answer
43
views
MariaDB - Recursive Query with condition on last recursion level
given a MariaDB database with information about S/MIME certificates and the following database structure:
Table: cm_certificates
cm_id
cm_not_after
cm_issuer
cm_subject
cm_store_name
1
2028-09-20 08:...
1
vote
1
answer
73
views
Clickhouse generate X rows of Y random numbers in an array
I would like to extend the below to return multiple rows, where each row has a different array of random values
with base as (
-- makes a random number 0 to 50, adds up to 10 into an array
...
- 23