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

learn more… | top users | synonyms (1)

1
vote
1answer
36 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
15 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
79 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
30 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
55 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
76 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
59 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
0answers
74 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
51 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
66 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 ...
1
vote
1answer
215 views

Count how many child a parent has in a recursive cte

This is the query I'm using to find comments by a parent id WITH RECURSIVE cte (id, content, path, parent_id, depth) AS ( SELECT id, content, array[id] AS path, ...
6
votes
0answers
143 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 ...
0
votes
2answers
116 views

what is the quickest way to delete duplicate rows?

I need to delete duplicate rows from a large table. what is the best way to achieve that? currently I use this algorithm: declare @t table ([key] int ) insert into @t select 1 insert into @t ...
3
votes
1answer
483 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
26 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
96 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
126 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 ...
0
votes
1answer
64 views

List of role permissions including other roles - how can I find out the roles that belong to other roles?

I can see the permissions of a role in a database using the scripts in List all permissions for a given role? However, It does not show me when the role belongs to a another role. For ...
1
vote
2answers
88 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
899 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
43 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, ...
7
votes
1answer
175 views

Bulk insert M:N relation in PostgreSQL

I need to import data from an old database to a new one, with slightly different structure. For example, in the old database, there is a table recording employees and their supervisors: CREATE TABLE ...
-1
votes
1answer
71 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
587 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
69 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
206 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
130 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 ...
2
votes
2answers
1k 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
457 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
286 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
2k 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
129 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
441 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
192 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
734 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
133 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
49 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
307 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
124 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
138 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
93 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
94 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
77 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
248 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 ...
3
votes
1answer
297 views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
5
votes
2answers
360 views

Managing CTE execution order under uncertainty

I am writing a large, multi-step CTE for performance reasons. In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero. In a ...
1
vote
1answer
272 views

T-SQL View — How to 'pre-fetch' schema using scalar function, then populate using table query

I have an application that uses T-SQL views. A number of these views are quite complex, joining data from many tables. To avoid column-name collisions, the views often employ table-scalar functions to ...
5
votes
2answers
1k views

CTE works as expected, but not when wrapped into a function

--testing table CREATE TABLE public.test_patient_table ( entity_id INTEGER NOT NULL, site_held_at INTEGER NOT NULL, CONSTRAINT entityid_pk PRIMARY KEY ...
1
vote
2answers
103 views

Insert New Foreign Key Row for new Strings

Lets say I have a table new_data like this: ndx | someval | somemachine serial | integer | integer ---------------------------- And let's say I have a reference table new_data_someval_ref: ...