Questions tagged [cte]

Acronym for Common Table Expression. Temporary, reusable subquery that may be recursive.

Filter by
Sorted by
Tagged with
2 votes
1 answer
202 views

Common Table Expression syntax

Considering a query with CTEs: WITH cte_0 AS ( SELECT SUM(col_0) FROM table_0 WHERE condition_00 AND condition_01 ), cte_1 AS ( SELECT SUM(...
jvkloc's user avatar
  • 125
1 vote
1 answer
49 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, ...
Outtruder's user avatar
  • 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 ...
surfmuggle's user avatar
1 vote
1 answer
85 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 ...
egoz's user avatar
  • 113
1 vote
1 answer
83 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 ( ...
Bart Jonk's user avatar
  • 293
0 votes
2 answers
161 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(*) ...
gbox's user avatar
  • 111
0 votes
1 answer
113 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 ...
sujeet's user avatar
  • 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 ...
jonatan hellborg's user avatar
0 votes
1 answer
351 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 ...
jcollum's user avatar
  • 207
-2 votes
1 answer
90 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'?
Mahmoud fathy's user avatar
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 ...
Michelle's user avatar
0 votes
1 answer
117 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: ...
thisisforfun's user avatar
0 votes
1 answer
142 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 ('...
sudoExclaimationExclaimation's user avatar
0 votes
2 answers
97 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 ...
guradio's user avatar
  • 97
1 vote
0 answers
146 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 ...
Manngo's user avatar
  • 2,769
1 vote
0 answers
41 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 ...
Nandakumar V's user avatar
1 vote
0 answers
102 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 ...
tomhgmns's user avatar
8 votes
2 answers
282 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 ...
alx9r's user avatar
  • 421
1 vote
0 answers
152 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 ...
MM3000's user avatar
  • 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 = ...
Don Draper's user avatar
2 votes
2 answers
624 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, ...
Ian Kemp's user avatar
  • 364
1 vote
2 answers
507 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(...
David Palacios's user avatar
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 ...
DbMajor's user avatar
  • 13
0 votes
0 answers
173 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 ...
awsBi's user avatar
  • 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) ;...
SlowMagic's user avatar
  • 141
1 vote
0 answers
47 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 ...
TheSQLGoat's user avatar
0 votes
1 answer
174 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 ...
user3621633's user avatar
1 vote
1 answer
550 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 ...
Subin Benny's user avatar
5 votes
1 answer
347 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 ...
RandomCorpSQLDev's user avatar
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 ...
David's user avatar
  • 31
1 vote
1 answer
604 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 -- ...
ahron's user avatar
  • 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 ...
Diya Rawat's user avatar
2 votes
0 answers
116 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. ...
Viz Krishna's user avatar
2 votes
2 answers
924 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 ...
DBA Greg14's user avatar
0 votes
1 answer
569 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 ...
ahron's user avatar
  • 779
1 vote
0 answers
145 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 ...
ahron's user avatar
  • 779
1 vote
1 answer
8k 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 ...
baxx's user avatar
  • 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 ...
Gthoma2's user avatar
  • 111
1 vote
1 answer
990 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 ...
Biller Builder's user avatar
0 votes
0 answers
296 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 ...
J Pablo F's user avatar
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 ...
ggambetta's user avatar
  • 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 ...
Edge's user avatar
  • 101
2 votes
2 answers
193 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 ...
Andy Stewart's user avatar
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 ...
Edge's user avatar
  • 101
1 vote
1 answer
262 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 ...
Biller Builder's user avatar
1 vote
1 answer
283 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 ...
Biller Builder's user avatar
1 vote
1 answer
275 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 ...
Lance's user avatar
  • 189
1 vote
1 answer
231 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 ...
fudo's user avatar
  • 111
0 votes
1 answer
933 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 ...
Findnix's user avatar
-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 ...
noob coder's user avatar

1
2 3 4 5
8