common-table-expression

SQL Server 2008 CTE Recursion

Hello. I am trying to perform what I believe is a difficult recursion using a CTE is SQL Server 2008. I can't seem to wrap my head around this one. In the below examples you can assume a fixed depth of 3...nothing will ever be lower than that. In real life, the depth is "deeper" but still fixed. In the example I tried to simplify it som...

Does SQL Server support the Oracle-like WITH clause?

I've seen mention of the Oracle WITH clause a few times around here for aliasing a subquery like this: WITH myData AS (SELECT id, text FROM SomeTable) SELECT myData from SomeOtherTable Does any version of SQL Server support this? If not, is there a particular reason they don't? Performance? Potential for incorrect usage? ...

Compute median of column in SQL common table expression

In MSSQL2008, I am trying to compute the median of a column of numbers from a common table expression using the classic median query as follows: WITH cte AS ( SELECT number FROM table ) SELECT cte.*, (SELECT (SELECT ( (SELECT TOP 1 cte.number FROM (SELECT TOP 50 PERCENT cte.number FROM cte ...

Common table expression functionality in SQLite

I need to apply two successive aggregate functions to a dataset (the sum of a series of averages), something that is easily and routinely done with common table expressions in SQL Server or another DBMS that supports CTEs. Unfortunately, I am currently stuck with SQLite which does not support CTEs. Is there an alternative or workaround...

Row_Number() CTE Performance when using ORDER BY CASE

I have a table I'd like to do paging and ordering on and was able to get a query similar to the following to do the work (the real query is much more involved with joins and such). WITH NumberedPosts (PostID, RowNum) AS ( SELECT PostID, ROW_NUMBER() OVER (ORDER BY CASE WHEN @sortCol = 'User' THEN User END DESC, CASE ...

Use Recursive CTE in DB2 stored proc

I have a need to run a recursive CTE within a stored proc, but I can't get it past this: SQL0104N An unexpected token "with" was found following "SET count=count+1; ". Expected tokens may include: "". LINE NUMBER=26. My google-fu showed a couple of similar topics, but none with resolution. The query functions as expected outside of the...

T-SQL CTE Error: Types don't match between the anchor and the recursive part

I get the following error when I try to execute a particular recursive CTE: Msg 240, Level 16, State 1, Line 8 Types don't match between the anchor and the recursive part in column "data_list" of recursive query "CTE". This is nonsense. Each field is explicitly cast to VARCHAR(MAX). Please help me. I've read many answers to this prob...

How many times are the results of this common table expression evaluated?

I am trying to work out a bug we've found during our last iteration of testing. It involves a query which uses a common table expression. The main theme of the query is that it simulates a 'first' aggregate operation (get the first row for this grouping). The problem is that the query seems to choose rows completely arbitrarily in some ...

Sql For Xml Path get node count

I'm trying to use the 'For Xml Path' T-SQL to generate a comma separated list of values from a column. This seems to work great, but the problem is I would like to get a count of the items in the comma separated list. Here is an example of the code I am using to generate the comma separated list: Create Table #List ([col] varchar) Inse...

Combine CTE "WITH" and a "WITH XMLNAMESPACES...." in SQL Server

Has anyone managed to create a CTE in SQL Server's T-SQL that also includes a WITH XMLNAMESPACES declaration? It seems both WITH keywords insist on being the "first in the T-SQL batch", and that doesn't really work.... I tried: WITH XMLNAMESPACES('http://schemas.myself.com/SomeSchema' as ns) WITH CTEQuery AS ( SELECT (list of fields) ...

How do I convert the following code to a SQL Server/T-SQL CTE?

I consider myself rather proficient with T-SQL and I'm usually able to optimize a query pretty good without loosing readability. In short: I like my SQL short, descriptive, declarative and elegant. While the following code works, i have two problems with it: I am using cursors and I can't shake the feeling I have in the back of my hea...

Why do my results not stay consistent?

I have the following stored procedure that I working on. I have noticed that every 5th or 6th time I refresh my results there are new values in there. Which considering that the data is in a static environment and no one is making any changes to the data at this time I really can't understand. Can someone please enlighten me as to why I ...

CTE concatenation missing first row in group iteration

I am attempting to concatenate multirow values into single row...I have achieved a close proximity but realize that the query below misses the first row every time...am I missing something obvious here? I have never used CTE before so I'm trying hard to understand the concepts behind it. Thanks in advance. WITH CTE ( AnswerResponseRefId...

SQL CTE and ORDER BY affecting result set

I've pasted a very simplified version of my SQL query below. The problem that I'm running into is that the ORDER BY statement is affecting the select results of my CTE. I haven't been able to understand why this is, my original thinking was that within the CTE, I execute some SELECT statement, then the ORDER BY should work on THOSE resul...

Recursive CTE to find parent records

Hello, first i must admit that i'm not very familiar with sql server's recursive CTE's but i think this is the best approach. I have a table tabData. Its PK is named idData and there is a self referencing FK fiData. So fiData references the parent record and SELECT * FROM tabData WHERE idData=fiData returns all data of the parent. T...

Wrong order in Table valued Function(keep "order" of a recursive CTE)

Hello, a few minutes ago i asked here how to get parent records with a recursive CTE. This works now, but I get the wrong order(backwards, ordered by the PK idData) when i create a Table valued Function which returns all parents. I cannot order directly because i need the logical order provided by the CTE. This gives the correct order(f...

Extremely slow Table-Valued-Function with recursive CTE inside.

Hello, I've cretaed a TVF which returns a table with parent-records from a recursive CTE here. That works excellent and the result is available directly. Now i wanted to get the child-records(they have the same FK as the current record's PK). The problem is that it takes 1:10 minutes to get 22 child-records for a given id. Why is this s...

Multi-part identifier could not be bound

I know that there are several questions around this exception on SO, but nothing seen that helps me. I have following query giving me a "Multi-part identifier 'claim.fiData' could not be bound"-Exception: SELECT claim.idData FROM tabData as claim INNER JOIN dbo._previousClaimsByFiData(claim.fiData) AS prevClaim ON prevClaim.idData...

Exiting from recursive common table expression once the result set contains some value

Given the following table: create table TreeNode ( ID int not null primary key, ParentID int null foreign key references TreeNode (ID) ) How could I write a common table expression to start at the root (WHERE ParentID IS NULL) and traverse its descendants until the result set contains some target node (e.g., WHERE ID = n)? It's ea...

Adding an INDEX to a CTE

Should be a pretty straight forward question. Can I add an INDEX to a Common Table Expression (CTE)? ...