Questions tagged [cte]

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

Filter by
Sorted by
Tagged with
-2
votes
1answer
47 views

Is there any possible way to modify the database state from within the scope of a CTE? [on hold]

For example: -- Directly with a DML Statement WITH CTE_DML AS ( UPDATE Table1 SET Column1 = 'Value1' FROM Table1 ) -- Indirectly by executing a procedure WITH CTE_SP AS ( EXEC ...
8
votes
2answers
312 views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
3
votes
2answers
107 views

Can subqueries produce different execution plans than CTE's?

I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test: ...
0
votes
1answer
25 views

How to insert values to one table from the table stored in another database in postgresql?

Here is the structure of table: `* Table 1 of database A contains mobile number and userId and both field cannot be blank I want to fetch data from table 1 of database A and insert that with some ...
2
votes
1answer
32 views

Why does DELETE not work after an UPDATE in a CTE?

CREATE table test ( id integer, x integer ); INSERT INTO test VALUES (1, 1); SELECT works: WITH res AS ( UPDATE test SET x = 10 WHERE id = 1 RETURNING id ) SELECT id FROM test WHERE ...
-1
votes
1answer
28 views

Advantage of 'with' queries over plain 'select' and joins?

Are there any advantages using WITH queries instead of simple SELECT queries using JOINs performance wise? I am asking solely about reading data (from catalogues specifically).
3
votes
1answer
67 views

Reused Nested Decision Logic - CTE vs Copying Code

Question I have a query which outputs one column, created through a series of CASE statements. That same column is used as part of a CASE logic for a 2nd column in the same SELECT statement. If I ...
1
vote
1answer
54 views

T SQL Cte Previous record calcuation

I'm using SQL Server 2016. I have the below table: SKU Shop Week ShopPrioirty Replen Open_Stk Open_Stk Calc 111 100 1 1 0 17 ...
0
votes
1answer
26 views

Reusing literal string in different parts of query

I created this PostgreSQL query while troubleshooting a problem that turned out to be due to non-printing characters in my strings: with cte as ( select 'The character between the xes is evil: xx' as ...
1
vote
1answer
103 views

UPDATE table using CTE

I wanted to update the ranks of my table Performance and I found this solution, but I do not understand why this SQL code works: WITH cte AS ( SELECT AllTimeRank, r = RANK () OVER (PARTITION BY ...
2
votes
3answers
110 views

Count Items not In Time Range

I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out. So, I have this table: WITH ADates AS ( ...
0
votes
0answers
5 views

Postgres break table columns into multiple tables having unique rows

I have this table called transactions. It has two columns id and report_id. id is primary key. I want to create two new tables transaction_ids and report_ids both with just one column id. Both these ...
0
votes
1answer
29 views

Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

I am getting "ERROR: out of shared memory" presumably because my plpgsql function drops/creates a lot of temporary tables. Searching for a solution to the issue, I found this answer that says: ...
0
votes
2answers
123 views

MySQL Create View WITH CTE

I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming. SQL SERVER Command: CREATE VIEW [dbo].[vw_PurchParent] as WITH cte AS ( SELECT a._Id,...
1
vote
1answer
56 views

Overlaps and Gap grouping in SQL Server

I am trying to find gaps in my data and then group those. The table I am working with looks like this: runid|year |road|start |end 01010| 9 |2 |0.000 |0.585 01100| 9 |2 |0.585 ...
2
votes
1answer
93 views

CTE Query to CONCAT text (over 10,000 rows)

I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber. Below is what I am working with: CREATE TABLE #SampleTable ( ...
0
votes
0answers
42 views

Cleaner approach for Recursive CTE query

I have a table called entry_changes defined as follows: Table "public.entry_changes" Column | Type | ...
0
votes
1answer
81 views

Unable to wrap a simple CTE in plpgsql loop

For a test database, I would like to create a bunch of test users (all with "default" values). There is a table called users, and another called user_properties. For each test user, I need to make ...
2
votes
1answer
72 views

Calculate difference between current value and next value without CTE/ inline table

I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits. With Visits AS ( SELECT [uid],[...
0
votes
0answers
43 views

Fulltext search in a CTE result - missing fulltext index

My question applies to MariaDB 10.2.7 The query WITH prefiltered AS ( SELECT id, fulltext_indexed_column FROM fulltext_table WHERE id < 30 ) SELECT * FROM prefiltered WHERE MATCH(...
0
votes
1answer
57 views

How to make use of WITH in PostgreSQL to reduce query cost

I am trying to get twitter data of narendramodi using below command. SELECT b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count,b.favorite_count as like_count,count(...
0
votes
1answer
57 views

Select All Instances of Substring in a String

I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId ...
3
votes
3answers
373 views

SQL Server CTE Bottom to Top Recursive with Where clause

I have an Employee Table with an EmployeeId, ManagerId and a Name field. The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null). I found ...
1
vote
1answer
147 views

Correcting and Detecting overlapped dates with CTE

I have a CTE that marks (as bits) overlapped records based on certain criteria. After filtering, I need to update these records to "correct" them and make them sequential, but I'm getting a 4104 on ...
3
votes
2answers
206 views

Postgres CTE optimization with nested json_build_object

I'm trying to write a query that returns data from multiple tables and aggregates it in one nested JSON field. I feel like this would perform great on SqlServer but, as Brent Ozar wrote in this post, ...
1
vote
1answer
87 views

Rewrite SQL query in CTE query

I have this table which I would like to store a chain of records. CREATE TABLE table_name ( id INT, unique_id varchar, reference_id varchar, ); I want to implement SQL query for MariDB ...
1
vote
0answers
58 views

Is there a way to make postgres count planned number of rows when using recurcive CTE?

Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle: create table test_recursive ( id serial primary key, parent_id int null references test_recursive, name text ...
0
votes
0answers
209 views

PostgreSQL Optimize bulk values CTE and SELECT INTO TEMP TABLE to prevent OOM kills

Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be ...
19
votes
4answers
2k views

How does SQL recursion actually work?

Coming to SQL from other programming languages, the structure of a recursive query looks rather odd. Walk through it step by step, and it seems to fall apart. Consider the following simple example: ...
1
vote
0answers
30 views

Guarding against CTE infinite loops in JOIN condition considered bad practice?

In docs, and pretty much everywhere else on the net, to guard against loops in recursive CTEs people use cycle column. The problem with this solution (as opposed to doing the ANY(path) check in join ...
0
votes
2answers
59 views

What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

In docs, the join of non-recursive part to recursive is done via CROSS JOIN. Is there some benefit to using it instead of INNER JOIN? Is it just a subjective choice? CREATE TEMPORARY TABLE folders (...
0
votes
1answer
689 views

Generate Dates between Date Ranges in mysql

Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9. Is there an inbuilt function which I ...
2
votes
1answer
275 views

Backend Table/Row Locking Structure using CTE Queries

This question is an extension of a previous question that I asked. Table Locking When using Unions With the same query we are using that is causing blocking the various queries used in these UNIONS ...
2
votes
0answers
40 views

Unrolling an entity-matching table

I have a table with the following structure: create table pairs ( block text, left_entity int, right_entity int ); That table was the result of a record linkage process. One of the ...
0
votes
1answer
38 views

subtract missing Value from Last Value

Ok, so it's kind if difficult for me as a non-native english speaker to get this into writing, so please bear with me. I have a source-Table as following: CREATE TABLE [dbo].[Mailbox]( [...
1
vote
1answer
81 views

Performance: CTE in an “IN”-Operator

I've got a query with a CTE (getting all children of a parent) followed by a main statement using the "IN"-Operator to filter for the CTE's output. Roughly looks like this: WITH cte_table AS (...) ...
2
votes
1answer
77 views

WITH RECURSIVE with union among the recursive term

Is it possible to use WITH RECURSIVE queries in PostgreSQL where the recursive term is a union of different tables? My usecase is to extract some tuples from a table and some logical rules: And I ...
4
votes
3answers
665 views

Caching intermediary CTEs for multiple uses

I'm working on this huge query that I chose to use CTEs. Due to the complexity of this query, I ended up having to reuse intermediary query results more than once in subsequent queries. The problem I'...
10
votes
1answer
490 views

What Rules Determine When SQL Server Use a CTE as an “Optimization Fence”?

A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL: Two Important Differences Between SQL Server and PostgreSQL The first point (“...
0
votes
1answer
39 views

Query Locks Table And Takes Long Time to Run

I have a query that can take a long time to run. I tried running the same query in a test server and it runs very quick. However, in production it seems to hang and causes blocking of other queries. ...
1
vote
1answer
57 views

Delete duplicates and reroute referencing rows to new master

Given three tables: A users table with id's: ╔════╗ ║ id ║ ╠════╣ ║ 1 ║ ╟────╢ ║ 2 ║ ╟────╢ ║ 3 ║ ╚════╝ A purposes table with user_id's and name's: ╔════╤═════════╤══════════╗ ║ id │ user_id │ ...
1
vote
1answer
412 views

Distribute multiple payments to invoice lines

I'm having a problem allocating payments to invoice lines. We’re using MsSql 2014 Data looks like this: Invoice lines table (sales): lineId invoiceId value 1 1 100 2 ...
1
vote
0answers
100 views

Postgres recursive query not using trivial index on massive table

I've got a simple recursive query to find a tree of all posts: with recursive all_mentions(post_id, author_id, mentioned_id) as ( select post_id, author_id, mention_id from mentions where ...
5
votes
3answers
896 views

Does the 'quality' of query optimization differ by database provider? [closed]

I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in ...
3
votes
1answer
365 views

How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
0
votes
1answer
77 views

Better approach to optimize query

We have a previously designed query that functions fairly well but I am thinking there has got to be a better way of performing the following. Here is the query... WITH CTE1 ( COL1, COL2) AS (...
1
vote
3answers
299 views

SQL recursion and cte - dates timeseries

I need to build vector(s) of time series dates (say 10) for a subset of start dates. The following statement returns 10 consecutive dates starting from '2010-01-01'. ;with cte as (select 1 i union ...
1
vote
1answer
78 views

Sql query for all ascendent of a node

I have a table having records as below: ID Name parentId 1 A Null 2 B 1 3 C Null 4 D 3 5 E 4 I need out put in following format ID Name ParentName ...
0
votes
1answer
283 views

Am I compromising effiency by using CTE instead of indexed tables? [closed]

Consider scenario where I am creating multiple intermediate tables in CTEs before joining the last intermediate result to some other table. The intermediate results can use joines as well. The ...
0
votes
1answer
75 views

Create rows groups by using subgroups limits

I had originally posted this in SO but reasoned that it would be more appropriate here. I have a view in an sqlite database such as this: SQL Fiddle (http://www.sqlfiddle.com/#!5/ae95b/1) This ...