Questions tagged [cte]
Acronym for Common Table Expression. Temporary, reusable subquery that may be recursive.
392
questions
1
vote
1
answer
46
views
INSERT result for SELECT FROM join'ed tables just modified
I have the task to insert data into couple tables and return data from their intersection in one query. The tables are linked through specific fields (solditems refers to products and invoices, ...
- 113
1
vote
0
answers
15
views
Find best intersect (most elements) between Order (haystack) and Pricerules (needle)
Full disclosure
I have posted a greater scoped question at SO a couple of days ago with a bounty (no answers yet).
Scope here
How can a CTE be used to find a match with the most elements?
For the ...
- 209
1
vote
1
answer
83
views
Index is not used when doing a query with PostgreSQL CTE
Given I use the following table :
CREATE TABLE operations (
asset_id varchar(255) NOT NULL,
event_id varchar(255) NULL,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
and ...
- 113
1
vote
1
answer
81
views
How to use WITH in a better way to parametrise a date
I love to use CTEs to create nice and clear queries. However, I'm pretty sure the query I created is really inefficient.
Is there a better way to do this and keep things clear ?
with first_date as (
...
- 293
0
votes
2
answers
146
views
Performance difference using CTE and subquery
I have run the following queries.
CTE
WITH temp AS (SELECT id, timestamp FROM table group by timestamp, id)
SELECT COUNT(*) FROM temp WHERE timestamp = '2023-01-01 08:28:45'
Subquery
SELECT COUNT(*) ...
- 111
0
votes
1
answer
104
views
PostgreSQL Query Performance: Sorting Array Length vs. CTE
I'm having a significant performance difference between two PostgreSQL queries that I'm trying to understand and optimize. I have a table with around 2TB of data, and both queries use the sequential ...
- 213
0
votes
0
answers
9
views
Implementing First In / First Out for fuel transactions (such as purchases at varying prices and transfers between locations)
After being tasked with trying to improve the way my company keeps the fuel inventory, i hit a wall. My SQL knowledge is pretty basic and i don't even know where to start.
A little background:
The ...
0
votes
1
answer
311
views
Optimal way to get a total count of rows in a paged query in Postgres?
I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing ...
- 207
-2
votes
1
answer
85
views
I want to make a tree of parents only in sql server
My table in the database
How can I get my result as Where child = 'chl1'?
0
votes
0
answers
24
views
creating a CTE to filter previous CTE populaiton down. How to find new diagnosis of certain ICD 10 code?
The first CTE grabs all patients with the diagnosis codes for substance abuse, the second CTE should filter down to only NEW paitents.
Meaning of the people in the first CTE, which of thses people ...
- 1
0
votes
1
answer
105
views
How do I calculate a streak of data and show the data that corresponds to it on MySQL?
I'm currently working on a project with NBA stats and I want to check a player's longest streak of his highest amount of points on MySQL. I'm open to a 5-10 game streak to simplify it.
For example: ...
0
votes
1
answer
133
views
How can I recursively query a table containing LTREE to get the tree like structure when some of the parent paths are missing?
Lets say I have this table:
CREATE TABLE nodes (node_path ltree);
INSERT INTO nodes VALUES ('Top.Science');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO nodes VALUES ('...
0
votes
2
answers
91
views
oracle working with CTE with condition
DEFINE fromtable1= true;
WITH firstQuery AS (
SELECT columns FROM table
),
secondQuery AS (
SELECT columns FROM second_table
)
IF @fromtable1= true
BEGIN
SELECT *
FROM ...
- 97
1
vote
0
answers
136
views
Is it possible to limit infinte recursion in an Oracle CTE?
In a DBMS like Microsoft SQL, there is a default limit of 100 recursions, in a recursive CTE, which can be adjusted with:
WITH cte AS (
…
)
SELECT * FROM cte OPTION (MAXRECURSION …);
As far as I ...
- 2,759
1
vote
0
answers
39
views
Mysql8 Unable to order the result of WITH RECURSIVE query
I am using the WITH RECURSIVE query to generate a hierarchy of users. The data and the query used is shared in https://www.db-fiddle.com/f/oTbCcMQb5a4B6V96DAYJHG/0 . The query is returning the results ...
- 111
0
votes
0
answers
96
views
Mimicking a filesystem using PostgreSQL and LTree: how to write a query to insert multiple nodes at once
I'm struggling with how to insert a recursive materialised path using one transaction.
This is the situation:
I'm trying to mimic a filesystem of folders and files. I have a table representing nodes ...
- 1
8
votes
2
answers
275
views
How can I efficiently traverse graph data with this pattern?
I have some relations embodying a directed acyclic graph that includes patterns similar to the following:
I'm looking for an efficient way to traverse this graph data. Here is an example of the ...
- 421
1
vote
0
answers
149
views
How to write query to link complex Parent-Child relationships
We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a ...
- 11
0
votes
0
answers
107
views
Unexpected behavior of a Postgres query with a JSONB column update inside a CTE
WITH deleted_files AS (SELECT name
FROM files
WHERE id = $1),
updated_links AS (
UPDATE links l
SET object = ...
- 199
2
votes
2
answers
611
views
Is it possible to update an existing table in a single statement, using the output of a MERGE statement?
I know I can achieve this in many ways - this is a purely academic question to see if it's possible to do this in a single statement, in order to further my knowledge of SQL.
We're splitting an old, ...
- 364
1
vote
2
answers
490
views
Insert values and add values to foreign key
I have two tables:
CREATE TABLE est (
est_id serial PRIMARY KEY
, est_nom varchar(70)
);
CREATE TABLE bm (
id serial PRIMARY KEY
, est_nom int
, nom varchar(70)
, CONSTRAINT FK_bm_est FOREIGN KEY(...
1
vote
1
answer
111
views
Return value from WITH clause in PL/pgSQL
I am attempting to RETURN an integer from a plpgsql MERGE-related action that uses WITH. The value I want to RETURN is within the WITH statement below - the id. The error I receive with the below code ...
- 13
0
votes
0
answers
168
views
Make this recursive CTE more dynamic
I have this script that enables me to extract hierarchies and the description for each level however the number of levels has to be known beforehand to define the number of select queries necessary ...
- 1
0
votes
3
answers
2k
views
CTE with UNION ALL not working as expected
The query below seems simple and straightforward, yet it produces unexpected results.
CREATE TABLE #NUMBERS
(
N BIGINT
);
INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;...
- 141
1
vote
0
answers
46
views
Recursive CTE get
I have the following tables:
Table1
id
entity_id
value
Table2 - Same structure as Table1
id
entity_id
value
Table3
source_id
destination_id
entity_id
Table1 and Table2 are both partitions from ...
- 11
0
votes
1
answer
167
views
How does SQL Server know not to execute Anchor element in Recursion CTE for 2nd Iteration / 1st Recursion?
I've been reading up on the inner workings of CTE Recursion and I'm still not quite sure how SQL Server is implementing the Anchor element in their source code and how SQL Server Query Execution ...
- 275
1
vote
1
answer
545
views
How to find child and grandchildren tables of a main table by order
I have this script to return all child and grandchildren tables with their level from a database . How do I change this if I only want to return the child and grandchildren tables of a particular ...
- 57
5
votes
1
answer
343
views
CTE Error when installing sp_WhoIsActive
I have several identical (near as I can tell) SQL Servers where I've recently added sp_WhoIsActive (showing some folks how much I like this tool) but one of them will not let me create the stored ...
3
votes
1
answer
49
views
best practice for dependency roots
When a database has a dependency table showing the parent-child relationship of items...
I've mostly seen guides that assume the top level parent has a "parent id" field which is either zero ...
- 31
1
vote
1
answer
596
views
Why does a Postgres recursive CTE need the list of columns as argument?
This is kind of a subjective question.
If I have a recursive CTE something like:
WITH RECURSIVE r(x, y) AS ( ...
SELECT x, y -- select1
FROM t WHERE ..
UNION
SELECT t.x, t.y -- ...
- 779
0
votes
0
answers
60
views
How to show all the columns data in full outer join
I have 2 tables.
The Result is
Issue is EnteredBy=23 is not displaying because I am using CT1.EnteredBy.
How to display CT11.EnteredBy value instead of NULL
The Actual Query I am using is given ...
- 19
2
votes
0
answers
115
views
Problem with sysdepends while checking dependencies
I have this script to list all dependencies of all views in a database . I get the dependencies in the right order when I use sysdepends ,but I am missing a few dependencies while using sysdepends. ...
- 89
2
votes
2
answers
906
views
CTE vs. temp table for batch deletes
Two-part question here.
We have a large table (between 1-2 million rows) with very frequent DML operations on it. During low volume periods, we have an agent job to remove older rows to keep the ...
- 257
0
votes
1
answer
549
views
How can I handle cycles / infinite loops in recursive CTEs in MySQL
Cycles in graphs lead to infinite loops in CTEs.
Dealing with them in Postgres is straightforward.
Since 8.0 MySQL also allows for CTEs. How I can detect cycles and infinite loops in MySQL CTEs?
The ...
- 779
1
vote
0
answers
143
views
Difference between CTEs in MySQL and PostgreSQL
This is probably a bit of a lazy question, but I would appreciate any quick pointers.
I have used CTEs considerably in PostgreSQL. I know that since 8.0 MySQL supports CTEs and now I want to take a ...
- 779
1
vote
1
answer
7k
views
Missing FROM-clause entry for table in postgres CTE
Using the following data:
CREATE TABLE a (
x int
);
CREATE TABLE b (
y int
);
INSERT INTO a VALUES
(1), (2);
INSERT INTO b VALUES
(1), (1), (2), (2) ;
I'm trying to query as:
WITH a_cte ...
- 296
1
vote
0
answers
57
views
TSQL - retrieve all IDs in a tree for a given subnode
I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode, but I'm either programming it wrong, or ...
- 111
1
vote
1
answer
973
views
How to perform updates on newly created rows within the same transaction?
Fiddle link: https://dbfiddle.uk/EOE627Oa
Tables
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
login text ...
- 276
0
votes
0
answers
292
views
How to optimize (or get an alternative faster solution to) this recursive query that groups using recursive CTEs in Postgres?
I am on the quest to implement an NMS filter for labels in a Database.
For context, here's a link to my first question, where I explain what NMS is and what I'm trying to achieve (I will edit that ...
- 51
3
votes
1
answer
211
views
Valid for WITH clause to use existing table name?
Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is ...
- 133
0
votes
0
answers
91
views
Optimise query selecting latest result from result list for each client
I'm hoping to time optimise a currently working query for a large production healthcare database. My current code takes about ~30 seconds to run. MSSQL 2016.
Firstly, I'm selecting just patients from ...
- 101
2
votes
2
answers
190
views
How to order by cumulative value?
In my application widgets move step by step through a workflow. I want to list the widgets ordered by their step due-dates but I can't figure out how to do that.
There are many workflows. Each ...
- 123
0
votes
0
answers
1k
views
Generate dynamic number of columns from CTE
A hospital database (SQL Server 2016) has a table of visits to the hospital. If patients transfer to a different specialty, a new visit is made (but from a research perspective it's considered one ...
- 101
1
vote
1
answer
257
views
How to perform dummy multi-inserts with generate_series()?
Given the tables like this: https://dbfiddle.uk/Z8hOhnYG
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
);
CREATE TABLE profiles (
id bigint GENERATED ALWAYS AS ...
- 276
1
vote
1
answer
267
views
How do you write ordered multi-inserts wih CTEs?
With the table like this:
CREATE TABLE test_1 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TABLE test_2 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TABLE ...
- 276
1
vote
1
answer
272
views
A query, safe against concurrent writes with READ COMMITTED, also safe with SERIALIZABLE isolation level?
I have asked this question with a wonderfully informative answer:
How to have uniqueness constraints for structures across multiple tables?
The answer from Erwin Brandstetter suggests this:
WITH ...
- 189
1
vote
1
answer
227
views
Define a function on the fly as with CTE in MySQL
With CTE it is possible to define 'on he fly' custom tables that exists only during the execution of the current query: is it possible to do the same for functions?
Example use case
I have a query ...
- 111
0
votes
1
answer
927
views
Filtering a recursive CTE as view
i'm using MSSQL Server 2017 and have a CTE used in a view to get the root element of a hirarchical tree linked to one specific element.
If the CTE is used directly and the base elements are allready ...
- 3
-1
votes
1
answer
1k
views
Postgres: After inserting value returns primary key and need to update column using returned primary id
In a single query, I need to perform Insert and update on the insertion row. For example, I have a set of columns and its value to be inserted after getting the primary key, I need to update another ...
1
vote
1
answer
435
views
Looking for an alternative to a CTE that will work as a subquery in IF EXISTS
I have an IF EXISTS 'upsert' running fine by itself in it’s own stored proc. But when I try and use the same statement referencing a CTE, it doesn't recognize the CTE. I see in related post that I'm ...
- 15