Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [cte]

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

2
votes
1answer
50 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
10 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
24 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
42 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
120 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
48 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 ...
2
votes
2answers
48 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
37 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
33 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
40 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 ...
18
votes
4answers
1k 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
26 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
41 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
240 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
103 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 ...
1
vote
0answers
34 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
37 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
53 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
43 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
524 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
381 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
36 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
46 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
116 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
40 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
891 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
0answers
227 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
76 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
102 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
43 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
118 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
49 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 ...
32
votes
2answers
3k views

If a CTE is defined in a query and is never used, does it make a sound?

Do unused CTEs in queries affect performance and / or alter the generated query plan?
0
votes
0answers
453 views

Slow performance using CTE

The query looks something like this ; with stats as ( select * from openquery ([remote-server], 'select empid, stat_dt' from [statistics]') -- when run solo, returns 20K rows in 45 seconds ) , ...
5
votes
1answer
239 views

How to optimize a keyset pagination query with CTEs on a big table?

I tried to document myself as much as I could on the topic before coming here to bother you, but here I am anyway. We want to implement keyset pagination on this table: create table api.subscription ...
3
votes
1answer
408 views

What's the (Big O) computational complexity of a PostgreSQL recursive common table expression?

For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to ...
2
votes
2answers
79 views

Oracle grouped row counts to columns

I have a table which contains this data: - ID - PRIMARY KEY, INTEGER - PC_ID - INTEGER - USER_ID - INTEGER - PAYMENT_DATETIME - TIMESTAMP - FIO - VARCHAR2(255) When I do a SELECT I get this (example)...
1
vote
0answers
52 views

How to remove duplicate records from result set of owner

I'm working with one project like rental agreement system. There are two roles types of users (User Role) Signer = 0 Observer = 1 Anyone can register and create a document(which will be referred as ...
0
votes
2answers
43 views

Select starting node ID in CTE

I have following table: create table osoba( id int primary key, idNd int, nazwim nvarchar(500), ) Data looks like below: id idNd nazwim 1 NULL grandpa 2 1 child1 3 1 child2 4 2 ...
2
votes
1answer
129 views

What happens with WITH if later the query fails

I am by no means a database expert so I might be asking something dumb. So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a ...
0
votes
0answers
59 views

Alternative to Mutli-line Table Valued Functions

I currently have a relatively large MTVF returning a set of data, the code I have provided below is a small subsection of what I have in place. All but 1 part of my function is built from a Cte chain ...
0
votes
1answer
36 views

Using WITH clause with INSERT statement in POSTGRESQL

I have a requirement in which I need to get one column from another table and insert that column data with some other data into another table. Example: If the cust_id='11' then I need to get the ...
1
vote
3answers
96 views

PostgreSQL - maximum of sum over different months with ties over multiple years

This question is the PostgreSQL version of a question about MySQL 5.6 here. Originally, it was one question for both RDBMS's but it was suggested to me that, given the different capabilities of the ...
3
votes
1answer
87 views

PostgreSQL priority between CTE and real table

Is there some kind of priority ranks assigned to CTE and table name ? For instance, if I have a table called table_a in the public schema, and I create a CTE table named table_a using WITH, which ...
2
votes
1answer
51 views

Is non-dynamic scalar subquery evaluted for each row?

Is a scalar subquery that's independent on parent query (does not refer to the parent table) evaluated only once, or for each row, regardless? SELECT /* ... */ FROM t1 WHERE parent = ( ...
6
votes
1answer
225 views

Help with CTE Aggregating Children Recursively

I'm trying to create a query to calculate the "Available to Sell" qty of all items in inventory. In this case, an item can have current available inventory, but could also be a kit, which is comprised ...
0
votes
2answers
1k views

Conversion failed when converting the varchar value 'Jan 1 1900 12:00AM' to data type int

I'm getting the following error : Conversion failed when converting the varchar value 'Jan 1 1900 12:00AM' to data type int. I have tried casting the problematic field which is in my third CTE(CTE4)...
0
votes
1answer
461 views

UPDATE using CTE

I am trying to update the single table with 4 columns (run_id, fqcn, diff_value, cleared_run_id) with the following snippet: WITH cte AS ( SELECT diff.fqcn, diff.run_id, diff.cleared_run_id, ...
1
vote
0answers
169 views

Implementing a graph-like design on postgresql using CTEs and inheritance

This question is about attempting to model/design an elementary graph-like database within postgres. The starting point is that graphs have: Nodes Nodes have properties Node are connected by (bi/...
5
votes
1answer
642 views

Conditional INSERT with a nested CTE?

I am trying to figure out if there is a way I can make nested CTEs work for this particular case. Consider the following (highly contrived) scenario which is based on the actual application: there's ...