Stack Overflow
Stack Overflow

Questions tagged [common-table-expression]

A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE or DELETE statement. Formally named , according to the ISO SQL standard.

A Common Table Expression (CTE) is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

  • Create a recursive query (use the tag in that case).
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.
4144 questions
1
vote
1 answer

Recursive query with hasMany relationship

The table representation is a simplification of the actual problem, because it captures the situation but is easier to understand. Two tables hold the definitions of units of measurement. There is a table with all the unit symbols and a has-many…
user2180613
  • 739
  • 6
  • 21
1
vote
1 answer

Update statement on a cte with subquery

I have a CTE that basically filters data based on certain criteria, the thing is that after filtering I need to update these records to "correct" them but I get a 4104 on the UPDATE statement. When I call directly the variable I don't have problems…
Abdón Araya
  • 186
  • 1
  • 9
1
vote
2 answers

Historical table with start and end dates and pivot

I have a historical table. Conditions: min date from date_asgn to start the start_cycle end cycle second before the next date. Only need the latest date for that day, doesn't matter which role it's under. PKID ID CUSTID ROLE Date_ASGN …
Mr John
  • 231
  • 1
  • 3
  • 18
1
vote
1 answer

SQL - Repeat the last known value until yesterday

I have a table with Date, ID and Value. For some ID, I don't have the value up to yesterday. I need to repeat the last known value for each ID until yesterday where it's NULL. I've already used a CTE to fill the NULL values between 2 non-NULL values…
Jerome
  • 21
  • 1
  • 6
1
vote
1 answer

Logical Processing of Recursive CTE

I don't understand why the anchor is not called when the recursive member calls the cte. Why does it go to the latest record (?) in the recursive part instead? WITH Managers AS ( --initialization SELECT EmployeeID, LastName, ReportsTo FROM…
John
  • 157
  • 5
  • 13
1
vote
2 answers

SQL Server : select only first instance of record with multiple columns

I'm trying to get some individual stats from a score keeping system. In essence, teams are scheduled into matches Match --------- Matchid (uniqueidentifier) SessionId (int) WeekNum (int) Those matches are broken into sets, where two particular…
tsweatt
  • 85
  • 2
  • 9
1
vote
2 answers

SQL Find Top Parent

I have this table: Id ParentId LevelName 1 0 XXXX 2 0 XXXX 4 2 XXXX 5 2 XXXX 6 2 XXXX 7 2 XXXX 8 2 XXXX 9 2 XXXX 18 2 XXXX 19 18 …
Rober
  • 73
  • 1
  • 8
1
vote
0 answers

Oracle WITH clause - force execute unused statement (disable optimization)

Problem: The Oracle DB uses some optimization and doesn't execute WITH component which isn't used in the final query. So no execution of unused queries. Question: Is it possible to disable Oracle's optimization and execute all queries in WITH…
jsosnowski
  • 1,560
  • 3
  • 26
  • 56
1
vote
2 answers

T-SQL: create stored procedure to update column in table with table and field as params in

I want to cleanse some columns in a table of a massive database with random data. I have created a stored procedure CreateRandomString to create random data (to replace client names etc so we can use the data as a demo). Now I want to create the…
MicWit
  • 655
  • 12
  • 31
1
vote
2 answers

Apply increase to previous row, using field from current row

I have a temp table set up like this: Type Rate TotalCost ---- ---- --------- Type1 0.01 3276.00 Type2 0.01 3276.00 Type3 0.01 3276.00 However, I need to take the rate of the current row, times it by the previous total,…
vinco83
  • 467
  • 1
  • 4
  • 16
1
vote
1 answer

SQL Server to check all columns in a databases views for a specific string value

My employer has a third party application that has a very complex set of obliquely named views. I am trying to locate the views that contain specific data that is entered in the applications UI. I have SQL to build a CTE with all view names and…
MWest
  • 13
  • 4
1
vote
4 answers

UNION & ORDER two tables inside Common Table Expression

I have a CTE inside a SQL Stored Procedure that is UNIONing values from two databases - the values are customer numbers and that customer's last order date. Here is the original SQL - ;WITH CTE_last_order_date AS ( SELECT c1.customer…
Optimaximal
  • 545
  • 1
  • 5
  • 28
1
vote
1 answer

SQL Weird Grouping - Matching rows sharing common values for either of two columns?

I am trying to use a recursive CTE to add a Group ID column to my data for mapping purposes. I am using the code here (SQL Server Weird Grouping Scenario by multiple columns and OR), but the output from this code is not working as intended. I have a…
Zak Fischer
  • 151
  • 1
  • 8
1
vote
1 answer

I updated to MariaDB 10.2.20 to use CTE. Still getting "Unrecognized Statement type. (near WITH) in phpMyAdmin

I am wanting to use CTE so I updated to MariaDB 10.2.20. phpMyAdmin is giving an error on the use of "WITH". I am not able to find why "WITH" is not supported in this version. MariaDB starting with 10.2.1¶ Common Table Expression WITH was…
user1956040
  • 111
  • 2
  • 12
1
vote
0 answers

How to retrieve multiple child categories from multiple parent categories in SQL database

I'm currently retrieving the expense categories having multiple parent categories with their child categories and so on. For that i used common table expression in order to apply recursion but unable to retrieve the desired result. This is the the…
Salar Muhammad
  • 334
  • 1
  • 8
  • 21
1 2 3
…
99
100