Skip to main content

Questions tagged [cte]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
27 views

PostgreSQL CTE behavior influenced by outer `update` statement: Why?

tl;dr Why does an update from ... statement make a CTE behave differently than an update where... with subquery statement in postgresql? Full Context Disclaimer: The information below has been ...
λ Jonas Gorauskas's user avatar
1 vote
1 answer
59 views

Getting sum for each day based on most recent timestamp entry for each entity

I have a table that contains an ID, timestamp, and integer. Each row is a "snapshot" of the entity at a given time: TableA ID timestamp num A 2024-02-04 5 B 2024-02-04 3 C 2024-02-07 8 ...
Timothy Fisher's user avatar
0 votes
0 answers
22 views

When generating combinations using a CTE, is there a way to use an index to group the elements of each combination?

I have a project that involves combining a variety of things into their possible combinations. This includes permutations, combinations, with and without repetition, and some more exotic ways of ...
alx9r's user avatar
  • 431
2 votes
2 answers
236 views

How to make a Data Modifying CTE abort if an update only partially succeeded?

I am wondering if it is possible to add some kind of check to make a data modifying CTE abort if an update is partially successful. Attached is a minimal query that represents the problem. I would ...
cynodon's user avatar
  • 23
0 votes
0 answers
39 views

Recursive CTE hiearchy snowflake -- join/ expand outward (columns) instead of rows?

I'm doing a dependency mapping explosion (like parts explosion). I did a typical recursive CTE with a union all. It looks like with CTE as ( select abc from myTable where start_point = X union all ...
user45867's user avatar
  • 1,683
1 vote
0 answers
111 views

Why is PostgreSQL performing a sequential scan, except when my CTE is materialized?

The issue My application is encountering performance issues on a query that, to the best of my understanding, should be performant. In this post, I have simplified the query and schema while still ...
sgielen's user avatar
  • 111
1 vote
2 answers
458 views

How can I optimize a recursive CTE inside a IVTF?

I have a recursive CTE inside a inline table-valued function. The ITVF is returning a list of IDs containing a long sequence of ancestors for a person, it usually loops back about 12 to 18 times ...
James McGrath's user avatar
0 votes
1 answer
54 views

Unable to use INSERT within CTE

I am trying to use a CTE to insert some random data into a table - create table foo (id integer) with x as (select random()) insert into foo (id) select x from x This gives an error: ERROR: ...
ahron's user avatar
  • 813
3 votes
1 answer
222 views

Help with CTE syntax in SQL

I am new to SQL, I tried to create a CTE, but I get a red squiggle under the AS and I am getting an error message in the process of executing the syntax: Msg 102, Level 15, State 1, Line 117 ...
Christine K's user avatar
0 votes
0 answers
24 views

How to match rows from relationship via a recursive CTE

Apologies for the terrible question title, I couldn't think how to describe this question in a single line. I have a schema that looks roughly like this: https://dbdiagram.io/d/...
WilliamMayor's user avatar
0 votes
1 answer
41 views

Return the longest consecutive sequence of a value in specific column

I am using PostgreSQL 12.0 and trying to get the longest sequence of continuous rows for a specific column and value. The table is called team2 and contains a team's results which looks like: match_id ...
seevans38's user avatar
2 votes
1 answer
210 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
  • 133
1 vote
1 answer
62 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
2 votes
0 answers
20 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
266 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
91 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
  • 317
0 votes
2 answers
1k 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
  • 121
0 votes
1 answer
304 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
  • 245
0 votes
0 answers
11 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
3k 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
161 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
1 vote
1 answer
32 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
274 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
366 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 ('...
sudoExclamationExclamation's user avatar
0 votes
2 answers
213 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
226 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,849
1 vote
0 answers
77 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
190 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
491 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
  • 431
1 vote
0 answers
182 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
2 votes
2 answers
982 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
960 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
146 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
213 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
1 vote
3 answers
4k 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
  • 151
1 vote
0 answers
50 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
231 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
788 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
448 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
4 votes
1 answer
57 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
  • 41
1 vote
1 answer
842 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
  • 813
0 votes
0 answers
69 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
148 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
1k 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
965 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
  • 813
3 votes
0 answers
216 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
  • 813
1 vote
1 answer
11k 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
  • 306
1 vote
0 answers
59 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
1k 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
347 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

1
2 3 4 5
9