I need to get an ordered hierarchy of a tree, in a specific way. The table in question looks a bit like this (all ID fields are uniqueidentifiers, I&#39;ve simplified the data for sake of example):
&lt;pre&gt;EstimateItemID    EstimateID    ParentEstimateItemID     ItemType
--------------    ----------    --------------------     --------
       1              A                NULL              product
       2              A                  1               product
       3              A                  2               service
       4              A                NULL              product
       5              A                  4               product
       6              A                  5               service
       7              A                  1               service
       8              A                  4               product&lt;/pre&gt;

Graphical view of the tree structure (* denotes &#39;service&#39;):
&lt;pre&gt;
           A
       ___/ \___
      /         \
    1            4
   / \          / \
  2   7*       5   8
 /            /
3*           6*
&lt;/pre&gt;

Using this query, I can get the hierarchy (just pretend &#39;A&#39; is a uniqueidentifier, I know it isn&#39;t in real life):

    DECLARE @EstimateID uniqueidentifier
    SELECT @EstimateID = &#39;A&#39;
    
    ;WITH temp as(
    	SELECT * FROM EstimateItem
    	WHERE EstimateID = @EstimateID

    	UNION ALL

    	SELECT ei.* FROM EstimateItem ei
    	INNER JOIN temp x ON ei.ParentEstimateItemID = x.EstimateItemID
    )
    
    SELECT * FROM temp
This gives me the children of EstimateID &#39;A&#39;, but in the order that it appears in the table. ie:

&lt;pre&gt;EstimateItemID
--------------
      1
      2
      3
      4
      5
      6
      7
      8&lt;/pre&gt;
Unfortunately, what I need is an ordered hierarchy with a result set that follows the following constraints:
&lt;pre&gt;
1. each branch must be grouped
2. records with ItemType &#39;product&#39; and parent are the top node 
3. records with ItemType &#39;product&#39; and non-NULL parent grouped after top node 
4. records with ItemType &#39;service&#39; are bottom node of a branch
&lt;/pre&gt;
So, the order that I need the results, in this example, is:

&lt;pre&gt;EstimateItemID
--------------
      1
      2
      3
      7
      4
      5
      8
      6
&lt;/pre&gt;

What do I need to add to my query to accomplish this?

CTE Recursion to get tree hierarchy

Can I add an INDEX to a Common Table Expression (CTE)?

Adding an INDEX to a CTE

I have come to understand that some versions of Microsoft OLE DB Provider for SQL Server (mostly on Windows XP) do not support `WITH` statement. So, I decided to move my SQL statement into a table-valued function, and call it from my application. Now, I&#39;m stuck. How should I use the `INSERT INTO` statement with `WITH`? Here&#39;s the code I have come with so far, but SQL Server doesn&#39;t like it... :-(

    CREATE FUNCTION GetDistributionTable 
    (
    	@IntID int,
    	@TestID int,
    	@DateFrom datetime,
    	@DateTo datetime
    )
    RETURNS 
    @Table_Var TABLE 
    (
    	[Count] int, 
    	Result float
    )
    AS
    BEGIN
    INSERT INTO @Table_Var ([Count], Result) WITH T(Result)
         AS (SELECT ROUND(Result - AVG(Result) OVER(), 1)
             FROM RawResults WHERE IntID = @IntID AND DBTestID = @TestID AND Time &gt;= @DateFrom AND Time &lt;= @DateTo)
    SELECT COUNT(*) AS [Count],
           Result
    FROM   T
    GROUP  BY Result
    	
    	RETURN 
    END
    GO

How to use a CTE statement in a table-valued function in SQL Server

In this excellent [SO question][1], differences between `CTE` and `sub-queries` were discussed.

I would like to specifically ask: 

In what circumstance is each of the following more efficient/faster?

- CTE
- Sub-Query
- Temporary Table
- Table Variable

Traditionally, I&#39;ve used lots of `temp tables` in developing `stored procedures` - as they seem more readable than lots of intertwined sub-queries.

`Non-recursive CTE`s encapsulate sets of data very well, and are very readable, but are there specific circumstances where one can say they will always perform better? or is it a case of having to always fiddle around with the different options to find the most efficient solution?
 

----------

**EDIT**  

I&#39;ve recently been told that in terms of efficiency, temporary tables are a good first choice as they have an associated histogram i.e. statistics.


  [1]: https://stackoverflow.com/questions/706972/difference-between-cte-and-subquery

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

I have begun reading about *Common Table Expression* and cannot think of a use case where I would need to use them. They would seem to be redundant as the same can be done with derived tables. Is there something I am missing or not understanding well? Can someone give me a simple example of limitations with regular select, derived or temp table queries to make the case of CTE? Any simple examples would be highly appreciated.

When to use Common Table Expression (CTE)

I have a MS SQL CTE query from which I want to create a temporary table. I am not sure how to do it as it gives an `Invalid Object name` error.

Below is the whole query for reference

    SELECT * INTO TEMPBLOCKEDDATES FROM 
    ;with Calendar as (
        select EventID, EventTitle, EventStartDate, EventEndDate, EventEnumDays,EventStartTime,EventEndTime, EventRecurring, EventStartDate as PlannedDate
        ,EventType from EventCalender
        where EventActive = 1 AND LanguageID =1 AND EventBlockDate = 1
        union all
        select EventID, EventTitle, EventStartDate, EventEndDate, EventEnumDays,EventStartTime,EventEndTime, EventRecurring, dateadd(dd, 1, PlannedDate)
        ,EventType from Calendar
        where EventRecurring = 1
            and dateadd(dd, 1, PlannedDate) &lt;= EventEndDate 
    )
    select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle
    ,EventType from Calendar
    where (PlannedDate &gt;= GETDATE()) AND &#39;,&#39; + EventEnumDays + &#39;,&#39; like &#39;%,&#39; + cast(datepart(dw, PlannedDate) as char(1)) + &#39;,%&#39;
        or EventEnumDays is null
    order by EventID, PlannedDate
    option (maxrecursion 0)

I would appreciate a point in the right direction or if I can create a temporary table from this CTE query 

How to create Temp table with SELECT * INTO tempTable FROM CTE Query

    WITH y AS (
        WITH x AS (
            SELECT * FROM MyTable
        )
        SELECT * FROM x
    )
    SELECT * FROM y

Does something like this work? I tried it earlier but I couldn&#39;t get it to work.

Can you create nested WITH clauses for Common Table Expressions?

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other _related_ tables.

My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event.

My solution to this is to add a CTE that groups scheduled events and counts the number of participants.

This will allow me to join in that information per scheduled event. Keeping the query simple.

I like to keep my queries simple, however, If I ever in the future need to have additonal temporary results accessible during my simple query, what do I do?

I would really like it, if I could have multiple CTEs but I can&#39;t, right? What are my options here?

I&#39;ve ruled out views and doing things at the application data layer. I prefer to isolated my SQL queries.

Keeping it simple and how to do multiple CTE in a query

I have a very complex CTE and I would like to insert the result into a physical table. 

Is the following valid?

    INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos 
    (
        BatchID,
        AccountNo,
        APartyNo,
        SourceRowID
    )	    
    WITH tab (
      -- some query
    )    
    SELECT * FROM tab

I am thinking of using a function to create this CTE which will allow me to reuse. Any thoughts?

Combining INSERT INTO and WITH/CTE

From this post [How to use ROW_NUMBER in the following procedure?][1]

There are two versions of answers where one uses a `sub-query` and the other uses a `CTE` to solve the same problem.

Now then, what is the advantage of using a `CTE (Common Table Expression)` over a &#39;sub-query`(thus, more ***readable*** what the query is actually doing)

The only advantage of using a `CTE` over `sub-select` is that I can actually *name* the `sub-query`.  Are there any other differences between those two *when a CTE is used as a simple (non-recursive) CTE?*


  [1]: https://stackoverflow.com/questions/706720/how-to-use-rownumber-in-the-following-procedure/706756#706756

Difference between CTE and SubQuery?

I keep getting a **`max recursion error`** with this query.

At first I thought it was because a null was being returned and then it would try and match the null values causing the error however, I rewrote my query so nulls aren&#39;t returned and the error still occurs.

What would be the best way to rewrite this function, so that the error will not occur

    WITH EmployeeTree AS
    (
        SELECT 
            EMP_SRC_ID_NR Id, USR_ACV_DIR_ID_TE Uuid, 
            CASE Employees.APV_MGR_EMP_ID 
               WHEN Null THEN &#39;0&#39; 
               ELSE Employees.APV_MGR_EMP_ID 
            END as  ApprovalManagerId 
        FROM 
            dbo.[tEmployees] as Employees WITH (NOLOCK)
        WHERE 
            APV_MGR_EMP_ID = @Id 
            and Employees.APV_MGR_EMP_ID is not null 
            and Employees.EMP_SRC_ID_NR is not null  
    
        UNION ALL

        SELECT 
            EMP_SRC_ID_NR Id, USR_ACV_DIR_ID_TE Uuid, 
            CASE Employees.UPS_ACP_EMP_NR 
               WHEN Null THEN &#39;1&#39; 
               ELSE Employees.UPS_ACP_EMP_NR 
            END as ApprovalManagerId 
        FROM 
            dbo.[tEmployees] as Employees WITH (NOLOCK)
        WHERE 
            UPS_ACP_EMP_NR = @Id 
            and Employees.APV_MGR_EMP_ID is not null 
            and Employees.EMP_SRC_ID_NR is not null  
        
        UNION ALL

        SELECT 
            Employees.EMP_SRC_ID_NR, Employees.USR_ACV_DIR_ID_TE, 
            CASE Employees.APV_MGR_EMP_ID 
                WHEN Null THEN &#39;2&#39; 
                ELSE Employees.APV_MGR_EMP_ID 
            END  
        FROM 
            dbo.[tEmployees] as Employees WITH (NOLOCK)
        JOIN 
            EmployeeTree ON Employees.APV_MGR_EMP_ID = EmployeeTree.Id 
        where  
            Employees.APV_MGR_EMP_ID is not null 
            and Employees.EMP_SRC_ID_NR is not null             
    )
    SELECT 
        Id AS [EmployeeId], 
        Uuid AS [EmployeeUuid], 
        ApprovalManagerId AS [ManagerId] 
    FROM EmployeeTree  	     





The maximum recursion 100 has been exhausted before statement completion

Which are more performant, `CTE` or `Temporary Tables`?

Which are more performant, CTE or temporary tables?

I would like to &quot;declare&quot; what are effectively multiple TEMP tables using the WITH statement. 
The query I am trying to execute is along the lines of:

    WITH table_1 AS (
    SELECT GENERATE_SERIES(&#39;2012-06-29&#39;, &#39;2012-07-03&#39;, &#39;1 day&#39;::INTERVAL) AS date
    )
    
    WITH table_2 AS (
    SELECT GENERATE_SERIES(&#39;2012-06-30&#39;, &#39;2012-07-13&#39;, &#39;1 day&#39;::INTERVAL) AS date
    )
    
    SELECT * FROM table_1
    WHERE date IN table_2

I&#39;ve read [PostgreSQL documentation][1] and researched into using multiple `WITH` statements and was unable to find an answer. 



  [1]: https://www.postgresql.org/docs/9.5/static/functions-srf.html

How to use multiple WITH statements in one PostgreSQL query?

I am converting all my SQL Server queries to MySQL and my queries that have `WITH` in them are all failing. Here&#39;s an example:

    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.article_isdeleted = 0
    )
    SELECT t1.*
    FROM t1
    ORDER BY t1.article_date DESC
    LIMIT 1, 3


How do you use the &quot;WITH&quot; clause in MySQL?

I never use CTE with recursion. I was just reading an article on it. This article shows employee info with the help of Sql server CTE and recursion. It is basically showing employees and their manager info. I am not able to understand how this query works. Here is the query:

    WITH
      cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
      AS
      (
        SELECT EmployeeID, FirstName, LastName, ManagerID, 1
        FROM Employees
        WHERE ManagerID IS NULL
        UNION ALL
        SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
          r.EmpLevel + 1
        FROM Employees e
          INNER JOIN cteReports r
            ON e.ManagerID = r.EmpID
      )
    SELECT
      FirstName + &#39; &#39; + LastName AS FullName,
      EmpLevel,
      (SELECT FirstName + &#39; &#39; + LastName FROM Employees
        WHERE EmployeeID = cteReports.MgrID) AS Manager
    FROM cteReports
    ORDER BY EmpLevel, MgrID

Here I am posting about how the output is showing:
![enter image description here][1]


  [1]: http://i.stack.imgur.com/03JpM.jpg

I just need to know how it is showing manager first and then his subordinate in a loop.
I guess the first sql statement fires only once and that returns all employee ids.

And the second query repeatedly fires, querying the database on which employee exists with the current manager id.

Please explain how the sql statement executes in an internal loop and also tell me the sql execution order. Thanks.

MY 2nd phase of question
--------------------------
    ;WITH Numbers AS
    (
        SELECT n = 1
        UNION ALL
        SELECT n + 1
        FROM Numbers
        WHERE n+1 &lt;= 10
    )
    SELECT n
    FROM Numbers

Q 1) how is the value of N is getting incremented? if the value is assigned to N every time then N value can be incremented but only the first time N value was initialized.

Q 2) CTE and recursion of employee relations:

The moment I add two managers and add a few more employees under the second manager is where the problem starts.

I want to display the first manager detail and in the next rows only those employee details that relate to the subordinate of that manager.

Suppose
----------

    ID     Name      MgrID    Level
    ---    ----      ------   -----
    1      Keith      NULL     1
    2      Josh       1        2
    3      Robin      1        2
    4      Raja       2        3
    5      Tridip     NULL     1
    6      Arijit     5        2
    7      Amit       5        2
    8      Dev        6        3


I want to display the results in such way with CTE expressions. Please tell me what to modify in my sql which I gave here in order to pull manager-employee relations. Thanks.

I want the output to be like this:
----------------------------------

    ID          Name   MgrID       nLevel      Family
    ----------- ------ ----------- ----------- --------------------
    1           Keith  NULL        1           1
    3           Robin  1           2           1
    2           Josh   1           2           1
    4           Raja   2           3           1
    5           Tridip NULL        1           2
    7           Amit   5           2           2
    6           Arijit 5           2           2
    8           Dev    6           3           2

Is this possible...?

SQL Server CTE and recursion example

I&#39;m trying to use MySQL to create a view with the &quot;WITH&quot; clause

    WITH authorRating(aname, rating) AS
       SELECT aname, AVG(quantity)
       FROM book
       GROUP BY aname

But it doesn&#39;t seem like MySQL supports this.

I thought this was pretty standard and I&#39;m sure Oracle supports this. Is there anyway to force MySQL to use the &quot;WITH&quot; clause?  I&#39;ve tried it with the MyISAM and innoDB engine.  Both of these don&#39;t work.


MySQL &quot;WITH&quot; clause

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions.

Declaring a single cte works fine.

    WITH cte1 AS (
        SELECT * from cdr.Location
        )
    
    select * from cte1 

Is it possible to declare and use more than one cte in the same SELECT?

ie this sql gives an error

    WITH cte1 as (
        SELECT * from cdr.Location
    )
    
    WITH cte2 as (
        SELECT * from cdr.Location
    )
    
    select * from cte1    
    union     
    select * from cte2


the error is

    Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword &#39;WITH&#39;.
    Msg 319, Level 15, State 1, Line 7
    Incorrect syntax near the keyword &#39;with&#39;. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

NB. I have tried putting semicolons in and get this error

    Msg 102, Level 15, State 1, Line 5
    Incorrect syntax near &#39;;&#39;.
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near &#39;;&#39;.

Probably not relevant but this is on SQL 2008.



How can I have multiple common table expressions in a single SELECT statement?

I want to insert data into 3 tables with a single query.  
My tables looks like below:

    CREATE TABLE sample (
       id        bigserial PRIMARY KEY,
       lastname  varchar(20),
       firstname varchar(20)
    );

    CREATE TABLE sample1(
       user_id    bigserial PRIMARY KEY,
       sample_id  bigint REFERENCES sample,
       adddetails varchar(20)
    );
    
    CREATE TABLE sample2(
       id      bigserial PRIMARY KEY,
       user_id bigint REFERENCES sample1,
       value   varchar(10)
    );

I will get a key in return for every insertion and I need to insert that key in the next table.  
My query is:

    insert into sample(firstname,lastname) values(&#39;fai55&#39;,&#39;shaggk&#39;) RETURNING id;
    insert into sample1(sample_id, adddetails) values($id,&#39;ss&#39;) RETURNING user_id;
    insert into sample2(user_id, value) values($id,&#39;ss&#39;) RETURNING id;

But if I run single queries they just return values to me and I cannot reuse them in the next query immediately.

How to achieve this?

Insert data in 3 tables at a time using Postgres

Is it possible to combine multiple CTEs in single query with `arel`? I am looking for way to get result like this:

    WITH cte1 AS (
    ...
    ),
    WITH RECURSIVE cte2 AS (
    ...
    ),
    WITH cte3 AS (
    ...
    )
    SELECT ... FROM cte3 WHERE ...

As you can see, I have one recursive CTE and two non recursive.

Multiple CTE in single query

I have the following CTE that will give me the DocTotal for the entire invoice.

     ;WITH CTE_DocTotal
     AS
     (
       SELECT SUM(Sale + VAT) AS DocTotal
       FROM PEDI_InvoiceDetail
       GROUP BY InvoiceNumber
     )

    UPDATE PEDI_InvoiceDetail
    SET DocTotal = CTE_DocTotal.DocTotal

Now with this result I want to enter into the column the DocTotal value inside PEDI_InvoiceDetail.

I know is not going to work and I know I am missing something, what is it?


Update records in table from CTE

I have this, and i get an error at set total.
Why can&#39;t i access a cte many times?

    ALTER PROCEDURE [dbo].[GetLeaguePlayers]
    (
    	@idleague int,
    	@pageNumber int,
    	@pageSize int,
    	@total int OUTPUT
    )
    AS
    WITH CTEPlayers AS
    (
    	SELECT ROW_NUMBER() OVER (ORDER BY p.Name) AS RowNumber, p.Id, p.Name, t.Name AS Team
    	FROM Players p INNER JOIN Teams t ON p.IdTeam=t.Id INNER JOIN Leagues l ON l.Id=t.IdLeague
    	WHERE l.Id=@idleague
    )
    SELECT Id, Name
    FROM CTEPlayers c
    WHERE RowNumber&gt;@pageSize*(@pageNumber-1) AND RowNumber&lt;@pageSize*@pageNumber;
    SET @total = ( SELECT COUNT(*) FROM CTEPlayers )

Use one CTE many times

I am executing the following statement:

    ;WITH cte AS (
      SELECT 
        1 as rn, 
        &#39;name1&#39; as nm
      UNION ALL
      SELECT 
        rn + 1,
        nm = &#39;name&#39; + CAST((rn + 1) as varchar(255))
      FROM cte a WHERE rn &lt; 10)
    SELECT * 
    FROM cte

...which finishes with the error...

    Msg 240, Level 16, State 1, Line 2
    Types don&#39;t match between the anchor and the recursive part in column &quot;nm&quot; of recursive query &quot;cte&quot;.

Where am I making the mistake?

CTE error: &quot;Types don&#39;t match between the anchor and the recursive part&quot;

How do you use `UNION` with multiple `Common Table Expressions`?

I&#39;m trying to put together some summary numbers but no matter where I put the `;`, I always get an error 

    SELECT  COUNT(*)
    FROM    dbo.Decision_Data
    UNION
    SELECT  COUNT(DISTINCT Client_No)
    FROM    dbo.Decision_Data
    UNION
    WITH    [Clients]
              AS ( SELECT   Client_No
                   FROM     dbo.Decision_Data
                   GROUP BY Client_No
                   HAVING   COUNT(*) = 1
                 )
        SELECT  COUNT(*) AS [Clients Single Record CTE]
        FROM    Clients;

I appreciate in the example above I can move the **single** CTE to the beginning, but I have a number of CTEs I&#39;d like to `UNION`

How do you UNION with multiple CTEs?

Usually in `SQL Server` Common Table Expression clause there is semicolon in front of the statement, like this:

    ;WITH OrderedOrders AS --semicolon here
    (
        SELECT SalesOrderID, OrderDate,
        ROW_NUMBER() OVER (ORDER BY OrderDate) AS &#39;RowNumber&#39;
        FROM Sales.SalesOrderHeader 
    ) 
    SELECT * 
    FROM OrderedOrders 
    WHERE RowNumber BETWEEN 50 AND 60

Why?

All Common Table-Expression Solutions on Gang of Coders

Total of 24 Common Table-Expression Solutions