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

learn more… | top users | synonyms (1)

11
votes
2answers
140 views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
2
votes
1answer
24 views

Limited utility in RECURSIVE VIEWS?

In the docs for recursive queries with WITH, you'll find this Recursive queries are typically used to deal with hierarchical or tree-structured data. This seems to be true, but how does this ...
0
votes
0answers
41 views

CTE query or sql query

I have two tables Table1 and Table2 Table1 Schema: Product | company | Demand...the demand is not present in table1. Table2 Schema: demand | company which contains demand ..... I need to update ...
7
votes
2answers
176 views

Looking for a simpler alternative to a recursive query

The actual query is more involved, but the problem I'm facing can be distilled to this: A query to filter a rowset of monotonically increasing integers so that - in the final result set, ...
4
votes
3answers
663 views

What's the difference between WITH CTE & WITH CTE (<column_names>)?

As shown in Using Common Table Expressions on MSDN, you can define a CTE as: WITH expression_name [ ( column_name [,...n] ) ] AS ( CTE_query_definition ) and use it like: SELECT ...
4
votes
1answer
59 views

How to get all connected values as a single column

I'm trying to use CTE recursion. This is my scenario SQL Fiddle. It is a simple tree structure: CREATE TABLE [dbo].[AL]( [IdAL] [int] IDENTITY(1,1) NOT NULL, [ID1] [int] NOT NULL, [ID2] ...
1
vote
0answers
23 views

CTE ordering guarantees [duplicate]

I'm starting to make heavy use of CTEs for my postgres based application, and am wondering if selecting from a previous CTE statement guarantees any ordering? E.g. is the order of rows for this query ...
4
votes
1answer
55 views

Optimizing “random” choice in PostgreSQL

I have the following table CREATE TABLE article ( id bigserial PRIMARY KEY, text TEXT NOT NULL, category_id bigint REFERENCES categories (id), status TEXT NOT NULL, locale TEXT ...
1
vote
1answer
64 views

Postgres CTE query for a single row not optimized

From my database I am trying to get a weighted sum of a user's stats, and I will only be querying the table one or two users at a time, so I wrote it as a view. Since it's a view, I pretend to ...
0
votes
0answers
16 views

PostgreSQL 9.4 CTE to count associated records using 2 columns

I need to count child records and cache the count in the parent record using a PostgreSQL 9.4 CTE. I have an Audit table and an Order table. The Audit table will keep a count of how many Orders ...
11
votes
2answers
166 views

PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
1
vote
1answer
33 views

Oracle database - get two rows (A,B) for every (COD) row

I have a Oracle database and a variable that can have a value for A and a value for B like in this example: cod cat v1 v2 v3 123 A 1 10 11 123 B 0 0 0 111 A 2 3 ...
2
votes
2answers
70 views

CTE To Use Alternate Select When Select Returns No Rows

I have created a involved CTE, no recursion, with multiple selects being done as it processes the business logic of the requirement. But I have been advised that the logic needed for the scenario ...
3
votes
1answer
134 views

For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4: gear_category ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); As well as a list of items ...
3
votes
1answer
73 views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 ...
1
vote
1answer
316 views

Teradata view with “With Clause” syntax

I am trying to a view with "With" clause in Teradata but it is not allowing to create a view. Please suggest with any options. Given below is the query which I have tried: create view ...
1
vote
0answers
74 views

SQL query using CTE not working after database migration

We have recently migrated an application, and in the process we have migrated from SQL Server 2005 to SQL Server 2008 R2. I have noticed that some SQL queries using CTE do not work anymore since. I ...
0
votes
1answer
82 views

How to find the price based on the given range

Here i need to find the price of the qty based on which price range the qty lies. In the first date(2015/01/01) 5 quantities have been sold and we made entries from (1 to 5) ,On second day for 10 ...
2
votes
1answer
348 views

Count children of a parent in recursive CTE

I use this query to find comments by a parent id: WITH RECURSIVE cte (id, content, path, parent_id, depth) AS ( SELECT id, content, array[id] AS path, parent_id, ...
6
votes
1answer
214 views

Surprising results for data types with type modifier

While discussing a recursive CTE solution for this question: Get the last 5 distinct values for each ID @ypercube stumbled across a surprising exception, which lead us to investigate the handling ...
3
votes
1answer
2k views

The equivalent of Common Table Expressions (CTEs) in MySQL

I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I ...
1
vote
0answers
31 views

How to parse and display row data as Column

I'm trying to display the row data from tbl_cte as a column. The result of column1 is alphanumeric e.g : "Data1.2.3". I need to parse it and display the "Data" as the name and "1.2.3" as the version ...
8
votes
1answer
147 views

How can I get the hierarchical values from the below query?

I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID. I need to get all the category IDs and their subcategory ...
2
votes
1answer
291 views

Add column with a sum total to crosstab() query in PostgreSQL 9.0

Following on from my previous question: Creating crosstab() pivot table in PostgreSQL 9.0 I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create ...
1
vote
2answers
127 views

Retrieve additional columns in recursive CTE

This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep. I manage ...
3
votes
3answers
2k views

No advantage of using Cross Apply or CTE over inline sub-query

I came across a query like this: SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C] FROM Items i I changed it to following ;WITH cte_count AS ( SELECT COUNT(1) c, OrderId ...
1
vote
2answers
44 views

Join Two queries with different declarations

I need to join two different queries to get some columns from both. The first one has a CTE. Query1: WITH StatusChanges AS ( SELECT HistoryID,PartID,Status, DateAdded, ...
-1
votes
1answer
76 views

CTE Expression not working

I am trying to get the employee hierarchy as output with empLevel indicating the reporting layer. However, my query returns only one row i.e. The CEO of the company while it's supposed to return all ...
2
votes
2answers
902 views

How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package?

I want to pass a parameter (@date1) in my SSIS OLE DB Source so I created a variable and I tried to pass the parameter using '?' but it's showing 'Syntax error, permission violation or other ...
2
votes
1answer
78 views

Wrong results from joining two CTEs

I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them. If I run the two CTE's as single queries each table ...
1
vote
1answer
282 views

Query for hierarchical RBAC scenario

I have a SQLite database with a hierarchical role-based access control (RBAC) data model. In this model, users are granted permissions to perform certain actions against various resources either ...
0
votes
1answer
217 views

Select records as groups based on sequence and date

I have data stored as: CompID | Seq | Udate | Utime --------+-----+-----------+------- mx433 | 1 | 1/1/2012 | 13:54 mx433 | 1 | 1/2/2012 | 13:43 mx433 | 2 | 1/4/2012 | 14:23 mx433 ...
3
votes
2answers
2k views

Using a CTE in IF EXISTS Query

Is it possible to do something similar to the following in SQL Server 2012? IF EXISTS ( WITH DATA AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn ...
-1
votes
1answer
785 views

Group by function not working correctly in SQL server CTE [closed]

This is SQL Server CTE, ;with grp (Sdate,TransactionType,tot) as (select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type], (count ([Transaction_type])) as [Total Count] ...
1
vote
0answers
378 views

Most efficient way to bulk insert or upsert data into multiple related tables

Given a PostgreSQL 9.4 schema with 3 related tables: CREATE TABLE a ( id BIGSERIAL PRIMARY KEY, a_name VARCHAR(80), a_field INTEGER ); CREATE TABLE b ( id BIGSERIAL PRIMARY KEY, b_name ...
6
votes
3answers
3k views

Check if any of the values is in a subquery result

I have a complicated subquery that returns a list of order IDs. I need to get a list of customers who have these orders. The problem is that there are two ways of assigning a customer to an order (one ...
0
votes
0answers
178 views

Optimizing a CTE query - overhead in inserting into a new table from select statement

I have two tables with the same structure like so (let us call them initial_table1 and initial_table2): tempid name num_records 1 a 2 2 b 2 3 c 2 4 d ...
2
votes
1answer
622 views

Updating table of versioned rows with historical records in PostgreSQL

I have a master table of versioned rows: CREATE TABLE master ( id SERIAL PRIMARY KEY, rec_id integer, val text, valid_on date[], valid_during daterange ); INSERT INTO master ...
0
votes
2answers
306 views

Match multiple components of a subquery CTE in PostgreSQL

I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out ...
0
votes
0answers
45 views

What is the difference between normal Select query and a Select query inside Common Table Expression(CTE)? [duplicate]

I'm having a table 'Products' and if Execute this query SELECT TOP 1000 [ProductID] ,[ProductDesc] ,[ManufacturingDate] ,[ExpiryDate] ,[Price] FROM ...
1
vote
1answer
1k views

join table or cte

First off, I've tried to find a suitable solution on the internet, but I really didn't find one that worked for me. What seems to be my problem? Well... At my new job we use MySQL. I'm used to ...
1
vote
1answer
159 views

Parent-child Relationship with data scattered between these records

I have a 60 column query that fills in a card on an application. The problem I am running into is gathering all the parent-child relationship data into one record per child. As a quick example: The ...
1
vote
1answer
52 views

Finding the current prices for n Fuelstations at a certain point in time

I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database. CREATE TABLE public.de_tt_priceinfo ( id integer NOT NULL DEFAULT ...
1
vote
1answer
438 views

Reuse SELECT query by adding results in array?

I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can ...
0
votes
1answer
146 views

Is there a reason my CTE with a basic subquery in the second stage of the CTE is failing?

Ok... anyone aid with this sorry...i've been playing with some of my works reports and i thought id found a way to mix two results into one when i came across a small issue... if i run my CTE code ...
0
votes
0answers
173 views

SQLite 3 Tree List Without CTE

I adapted a SQL statement to work with SQLite 3. But unfortunately Common Table Expressions and WITH clause works only in SQLite 3.6 ahead, but the users of my Open Source application are using SQLite ...
2
votes
1answer
112 views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
1
vote
1answer
101 views

Is it possible to emulate the WITH command in MySQL 5.1.73? [closed]

I have the following query: SELECT COUNT(*) AS `count` FROM agreements WHERE start_date >= STR_TO_DATE('14-01-2015','%d-%m-%Y') AND start_date < ...
3
votes
0answers
84 views

Can CTE sub-statements be prevented from executing at all?

Through this strategy, time consumed by queries for my application have dropped to extreme lows; however, some queries consume more milliseconds than necessary. The reason why is that upon ...
3
votes
1answer
297 views

Are CTEs multithreaded?

Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the ...