Questions tagged [cte]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
9 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 ...
0 votes
0 answers
17 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 votes
0 answers
27 views

CTE Performance with a large table

I have two tables - Sites (<100k records) and Visitors (>50 mil records). The queries below produce identical results, but the first one takes 150 milliseconds, and the second takes over a ...
  • 109
1 vote
1 answer
38 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 ...
1 vote
1 answer
30 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 ...
1 vote
1 answer
37 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 ...
  • 155
1 vote
1 answer
28 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
97 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 ...
-1 votes
1 answer
45 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
56 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
-1 votes
1 answer
40 views

Optimization : JOIN or CTE [closed]

Suppose that I have these tables,and example data (the real table I am working on has more columns) table `users` id name zip 1 John 201 table `region` zip city state country 22 a b c table `...
  • 23
3 votes
2 answers
412 views

CTE get all descendants with parents

Schema: CREATE TABLE item ( id int primary key, parent_id int, FOREIGN KEY(parent_id) REFERENCES item(id) ); INSERT INTO item (id, parent_id) VALUES (1, NULL), (2, 1), ...
-2 votes
2 answers
562 views

Detail on MS SQL CTEs

I am trying to find out how MS CTEs actually access data. Trying to find out if CTEs will actually run slower when pulling data and what is the order that a CTE will pull data. Does a CTE pull the ...
  • 19
3 votes
3 answers
525 views

Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?

In Postgres 13, I have a table which gets updated frequently. However, the update query is rather complicated and uses the same values multiple times. So, using a CTE seems quite a logical thing to do....
  • 309
2 votes
1 answer
162 views

postgresql Recursive Update resulting in Sequential Scan

I have a table with a self-referential foreign key, and I would like to update a given parent, and all of the descendants of the parent, with the same recursive update. When using an UPDATE with a ...
4 votes
2 answers
190 views

Index converted from Seek to Scan in CTE

SQL Server version: Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) Nov 2 2020 18:35:09 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-...
  • 279
1 vote
1 answer
120 views

CTE clause not able to be used in final ORDER BY statement

Trying to order by distance using an input geometry t(x), while comparing to geometries from a JOINed table. WITH "nearest_groups" as ( SELECT groups.id, ST_Distance(t.x, locations....
0 votes
1 answer
42 views

Can EXPLAIN be used to get some insights about Common Table Expression (CTE)?

I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL ...
  • 1,690
0 votes
0 answers
505 views

How to retrieve min/max values and corresponding dates in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data: CREATE ...
2 votes
1 answer
166 views

Recursive CTE in PostgreSQL to generate data with different frequencies

I am trying to write a data generation query which uses recursive CTE in Postgresql 14. Consider schema "sc" which includes a function getfreq. getfreq takes an int as param (which ...
0 votes
1 answer
3k views

ON CONFLICT DO UPDATE command cannot affect row a second time when trying to pass additional columns from a CTE in postgresql

Based on the SO: https://dba.stackexchange.com/a/46477 answer provided, I have been extending the Function with UPSERT in Postgres 9.5 or later for my own purposes to insert data into multiple ...
0 votes
1 answer
68 views

Difference between a query based on CTE and a simple query

i'm doing a select starting from a table named events that is joined with device measurements using a date between function (this time between is a sort of "mobile windowing") in order to ...
  • 181
0 votes
2 answers
221 views

Significance of $$ in MongoDB

I was recently given this snippet of code that generates an arbitrary document in MongoDB. I've never encountered $$ in Mongo before and I'm curious about its significance. All I know is that if I run ...
1 vote
3 answers
314 views

What are use cases for Recursive CTEs?

I am looking for examples of why you would use a recursive CTE, but the dozens of examples I have found in my web searching basically reduce to two: Generating a sequence Iterating through an ...
  • 2,377
0 votes
1 answer
47 views

How can I group / display same date data in one row?

I have a table that gets inserted into every night that is a snapshot of the data. At any point in time, the data might change in the columns (AccountNo stays the same, RunKey is incremented by 1, and ...
  • 15
0 votes
1 answer
46 views

How is the postgres actually getting some of the columns? Execution plan question

I've got a table with a bunch of columns. Among them facility_id and po_date. I'm writing a complex query and I have an index on those two columns. You can see from the planner output below that ...
  • 307
0 votes
1 answer
553 views

How to create multiple temp tables using records from a CTE that I need to call multiple times in Postgres plpgsql Procedure?

I am already using a CTE expression within a plpgsql Procedure to grab some Foreign Keys from (1) specific table, we can call it master_table. I created a brand new table, we can call this table ...
  • 111
0 votes
2 answers
58 views

Improving query (Doing a nasty self join on a range with ctes)

-- The CTE1 is just to create columns that dictate the bound of what is considered the same entry -- Also I do a dense rank by ACT_TIME, and a PARITION BY on ID1, ID2 -- so all ID1/ID2 combos are ...
0 votes
0 answers
51 views

JOIN vs WITH differences

apologize if there is the same question. When I started my career, my manager told me that you don't need to use "WITH", you can do anything with "JOIN". Now I am wondering is it ...
  • 109
1 vote
1 answer
503 views

Optimizing a recursive CTE or replacing it with a temporary table

I have a recursive query like this: with recursive PCte(id) as ( select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in ('123', '124','125', 'n') union all ...
0 votes
1 answer
264 views

MYSQL Error 1064 on INSERT INTO with CTE [closed]

I am trying to write a stored procedure to return a calculated value from a dataset by writing the result to a table. When I run this code on MySQL 8.0.32, I get an error 1064 Syntax error on the ...
  • 1
0 votes
2 answers
210 views

Update rows based on a recursive CTE query result

This query shows all lines recursively based on a uuid / parentUUID relationship: WITH RECURSIVE files_paths (id, parent) AS ( SELECT uuid, parentuuid FROM core_data WHERE uuid = '2c828bbb-...
0 votes
4 answers
269 views

Slow CTE update query

I have a query that is running for more than three hours without completing. I realized that the bad part is the Filter operator which has a cost of 89%. WITH FASES_NUMERADAS_CTE AS ( ...
1 vote
2 answers
392 views

SELECT... with LIMIT, but iterate forward getting other records?

I am doing a SELECT * FROM... with LIMIT query inside of a LOOP in my pgplsql function/procedure - something like this, e.g. $$ DECLARE BEGIN LOOP exit when get diagnostics n_rec_count = 0; ...
  • 111
2 votes
1 answer
165 views

Why my not-null columns become nullable in my CTE recursive query?

I have a simple hierarchical table. Let's say it has these columns: Id, [Guid], ParentId, Title Only ParentId is nullable. Other columns are not nullable. I have created a recursive query to show me ...
1 vote
2 answers
934 views

How can a cheap function in a SELECT make the whole query slow?

I'm using Postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts). I can't figure out why Query2 below is so much slower than Query1. They ...
  • 233
8 votes
2 answers
1k views

Nested CTEs Returning Incorrect Results in Certain Situations

OK, first let me say I got this issue from: Seeking Tsql wrong join explantion in CTE [closed] I was trying to assist to figure out the issue, but got stumped when trying to debug the code one step ...
0 votes
1 answer
280 views

How to eliminate duplicates made by recursive CTE [closed]

I have following schema in PostgreSQL 12 (objects and their relations are versioned by date ranges): CREATE TABLE tmp_deps ( id bigint, code text, name text, start_date date, ...
  • 123
-1 votes
1 answer
88 views

How to execute join statement dynamically without using static group id? [closed]

I work on SQL server 2014 I need to run this statement below dynamically without using group id static because I don't know maximum group id using may be 10,15,20 so How to run query below dynamically ...
5 votes
1 answer
600 views

How to turn a set of flat trees into a single tree with multiple leaves?

We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once: item_id jsonb_pretty 1 { "title": "PARENT", "...
  • 75
3 votes
1 answer
44 views

I would want, through recursive CTEs, a query to return all cars at tree-shaped, but sorted in each category

Here is both table and data for testing: CREATE TABLE if not exists cars ( id serial PRIMARY KEY, parent_code INT, descr CHARACTER VARYING(50) NOT NULL ); -- Level 1 INSERT INTO cars(...
  • 77
2 votes
1 answer
233 views

How can I change string output in a hierarchy tree with PostgreSQL?

I have a table like this: ------------------------------------------------- | id | description | parent_id | cost -------------------------------------------------- | 1 | Radiology ...
  • 37
1 vote
1 answer
214 views

How to merge multiple rows in one column with hierarchy Postgresql 12?

I have a table like this to save the catalog of 90 services: ------------------------------------------------------------------------------------- | id | service | subservice | description ...
  • 37
4 votes
1 answer
391 views

INSERT duplicate rows for multiple, cascading 1:M child tables

Imagine multiple parent-child tables with 1:M relationships. I would like to "cascade"-insert duplicate rows based on root parent table row selection. Each table has IDENTITY primary key and ...
  • 451
1 vote
1 answer
113 views

Order a nested table with path index according to list_order in SQLite

I have a SQL table with this nested structure: CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER); My SQLite version does not support window function,...
1 vote
1 answer
89 views

Can CTE simplify repeated and possibly recursive joins?

I have three tables organized as depicted below, showing foreign keys and unique fields (green): That represents email messages received by a server. (Sorry I couldn't simplify more. Full version ...
  • 115
2 votes
1 answer
123 views

Why can't unused columns and tables be ignored in a recursive CTE

Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and ...
  • 121
1 vote
1 answer
406 views

How do I return all column values for the recently inserted row/rows?

I have these two tables in a PostgreSQL 12 database: CREATE TABLE public.test ( entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4() ); CREATE TABLE public.test_registration ( entity_id uuid ...
  • 111
0 votes
1 answer
33 views

Getting Indirect parents

I have following different tables LeafChilds ChildId, ChildName 1, Child1 2, Child2 Group GroupId, GroupName 1, Group1 2, Group2 3, All GroupChildMapping GroupId, ChildId 1, 1 2, 1 2, 2 ...
  • 9
0 votes
0 answers
93 views

Improving query performance with two inner joins on tables that have > 40M rows

Context I have three tables - nodes, edges and sections. This is a graph network of paths from open street maps where edges are created from two neighbouring 3D nodes (that give distance, elevation ...

1
2 3 4 5
8