common-table-expression

Help with finding the difference (delta) from a value returned from the last two records

Hi, I'm using MS SQL 2005 and I have created a CTE query to return values from the last two records. I then use this to find the delta of two figures returned. I have a working query of sorts but I'm having problems getting anything other than the delta figure. here is my query: ;with data as( SELECT NetObjectID, RawSt...

Prevent recursive CTE visiting nodes multiple times

Consider the following simple DAG: 1->2->3->4 And a table, #bar, describing this (I'm using SQL Server 2005): parent_id child_id 1 2 2 3 3 4 //... other edges, not connected to the subgraph above Now imagine that I have some other arbitrary criteria that select the first and last edges, i.e. 1->2 a...

How can parallelism affect number of results?

I have a fairly complex query that looks something like this: create table Items(SomeOtherTableID int,SomeField int) create table SomeOtherTable(Id int,GroupID int) with cte1 as ( select SomeOtherTableID,COUNT(*) SubItemCount from Items t where t.SomeField is not null group by SomeOtherTableID ),cte2 as ( select t...

paged data using a CTE, how would nHibernate handle this?

Hi, I use CTE to handle paging of data currently, can criteria queries handle CTE? ...

Severe error when trying to FREETEXTTABLE an indexed view with a CTE

Hi, Where stockView is an indexed view with a full-text index, I receive the error message below. The database is running on a 2008 Express engine in 2005 compatibility mode. Code: with stockCte (title, grade, price, weighted) as ( select sv.[title] , sv.[grade] , sv.[price] , (case when sv.[issue] = ...

Recursive Query using CTE in SQL Server 2005

OK, here's what I'm trying to do. I'm using a CTE query in MSSQL2005. The objective of the query is to recurse through Parent child relationships of product categories and return the number of products under each category (this includes any products contained in children categories) My current version only returns the product count for ...

Find lowest common parent in recursive SQL table

Hi, Suppose I have a recursive table (e.g. employees with managers) and a list of size 0..n of ids. How can I find the lowest common parent for these ids? For example, if my table looks like this: Id | ParentId ---|--------- 1 | NULL 2 | 1 3 | 1 4 | 2 5 | 2 6 | 3 7 | 3 8 | 7...

Subsonic 3.0 Simplerepository : How to execute plain sql (CTE)?

I've been trying out Subsonic, but I've ran into a problem where I would like to be able to execute CTE towards by SimpleRepository and be able to get my POCOs back from that query. Is there any way to execute direct SQL in Subsonic 3.0, when using Simplerepository approach? To explain my problem in Northwind db example: I want to get ...

SQL Elaborate Joins Query

I'm trying to solve the below problem. I feel like it is possible, but I can't seem to get it. Here's the scenario: Table 1 (Assets) 1 Asset-A 2 Asset-B 3 Asset-C 4 Asset-D Table 2 (Attributes) 1 Asset-A Red 2 Asset-A Hard 3 Asset-B Red 4 Asset-B Hard 5 Asset-B Heavy 6 Asset-C Blue 7 Asset-C Hard If I am looking for something havin...

SQL Server 2005 recursive query with loops in data - is it possible?

I've got a standard boss/subordinate employee table. I need to select a boss (specified by ID) and all his subordinates (and their subrodinates, etc). Unfortunately the real world data has some loops in it (for example, both company owners have each other set as their boss). The simple recursive query with a CTE chokes on this (maximum r...

SQL query optimization (CTE + range function) in SQL Server 2005

I'm wondering whether such query could be potentially optimized. I've hugely simplified it, and you see the core of it. with Rec (Id,Name,ParentId) as ( select Id,Name,ParentId from Departments where ParentId is null union all select d.Id, d.Name, d.ParentId from Departments d join Rec r on (d.ParentId=r.Id) ) select q....

Reorder nodes in SQL Hierarchy data type

Using Hierarchy data type on SQL 2008. Nodes in my hierarchy go like this: value node 36 /8/1/ 38 /8/2/ 34 /8/3/ 40 /8/4/ 42 /8/5/ 44 /8/6/ 46 /8/7/ 48 /8/8/ I'd like to rearrange nodes so that /8/3/ and /8/1/ switch places. Any idea on how to do this? Only idea that I have so far is that I load all nodes o...

How to: select normalized view from denormalized data using CTE?

This is a very standard newbie question, but I am looking for an expert clever way to do this with little code. (I know how to do this long hand, with procedural code and cursors, so we can skip that part of the answer.) Basically, I have a de-normalized data set for which I need to extract a normalized table in a set processing way. T...

SQL Common Table Expression and an nested select

Group, I am looking for a suggestion to speed up my query. I am using a CTE to organize my data. I am pulling customer records based on account numbers, customertype and length they have been a customer. In the second part of the CTE I say COUNT(AcctNumber) AS Total and then I also want to do an additional nested select that will giv...

(SQL Server 2008) - SQL question regarding CTE and joins :-(

How can I retrieve the following results? user | date | login time | logoff time I wish to print out every day in a given time period with matching login and logoff time which is written is table history. If there's a multiple login for same user and for same day then SQL should select minimal date for login and maximum date for logof...

how do you Use the "With" Clause in MySQL

I am converting all my MSSQL queries to mysql and my queries that has "with" in them are all failing with t1 as ( SELECT article.*, userinfo.*, category.* FROM question INNER JOIN userinfo ON userinfo.user_userid=article.article_ownerid INNER JOIN category ON article.article_categoryid=category.catid WHERE article.ar...

SQL Server recursive CTE and paging

I have this table CREATE TABLE [dbo].[friend_blocked_list]( [subdomain] [varchar](50) NOT NULL, [un] [nvarchar](50) NOT NULL, [friend] [nvarchar](50) NOT NULL, [is_blocked] [bit] NOT NULL, [approved] [bit] NOT NULL) where i select data from it with below query. Select query combines users that added user as friend and users that...

Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon.

Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure WITH SomeClause1 AS ( SELECT .... ) WITH SomeClause2 AS ( SELECT .... ) But the error occurs Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a ...

how would you get many top root?

I have a parent/child relation table, many to many parentid int not null childid int not null let say I have company a, b, c, d, e a ..b ....c ..c d ..e ....b ......c I got this query to return one top parent FUNCTION [dbo].[getRootCompagny] ( @root int ) RETURNS int AS BEGIN DECLARE @parent int SELECT @parent = companyparen...

SQL 2005 CTE vs TEMP table Performance when used in joins of other tables.

I have a complex query that I need to use in a subsequent query (actually update statement). I have tried both using a CTE and a temp table. The performance using the CTE is horrible vs the temp table approach. Its something like 15 seconds vs milliseconds. To simplify the test instead of joining the CTE/Temp table in the subsequent q...