31

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows: Split the CTE expression into anchor and recursive members. Run the anchor member(s) creating the first invocation or base result set (T0). Run the recursive member(s) with Ti as an input and Ti+1 as an output. Repeat step 3 until an empty set is ...


26

See Martin Smith's answer for information about the current status of EXCEPT in a recursive CTE. To explain what you were seeing, and why: I'm using a table variable here, to make the distinction between the anchor values and recursive item clearer (it does not change the semantic). DECLARE @V TABLE (a INTEGER NOT NULL) INSERT @V (a) VALUES (1),(2) ; ...


26

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows: Split the CTE expression into anchor and recursive members. Run the anchor member(s) creating the first invocation or base result set (T0). Run the recursive member(s) with Ti as an input and Ti+1 as an output. Repeat step 3 until an empty set is ...


22

As I read the question, the basic recursive algorithm required is: Return the row with the earliest date in the set Set that date as "current" Find the row with the earliest date more than 90 days after the current date Repeat from step 2 until no more rows are found This is relatively easy to implement with a recursive common table expression. For ...


14

You need a recursive CTE (common table expression): with -- some DBMS (e.g. Postgres) require the word "recursive": with recursive -- some others (Oracle, SQL-Server) require omitting the "recursive": with -- and some (e.g. SQLite) don't bother, i.e. they accept both descendants as ( select parent, child as descendant, 1 as ...


14

It is great that you are taking the time to understand, classify and model the data you are dealing with since, from my personal experiencie, all this makes the whole development process easier and very flexible for future changes. And I am quite sure that you are also aware of this already. Preliminary data model and assumed business rules I defined a ...


14

The query you have is basically correct. The only mistake is in the second (recursive) part of the CTE where you have: INNER JOIN descendants d ON d.parent_id = o.object_id It should be the other way around: INNER JOIN descendants d ON d.object_id = o.parent_id You want to join the objects with their parents (that have already been found). So the query ...


14

This recursive CTE (SQL Fiddle) should work with your sample: WITH cte(ParentID) AS( SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet' UNION ALL SELECT i.ParentID FROM cte c INNER JOIN @Instances i ON c.ParentID = i.InstanceID WHERE i.ParentID > 0 ) SELECT ParentID, count(*) FROM cte GROUP BY ParentID ORDER BY ParentID ; Output ...


12

This is most easily implemented in SQL Server using a Recursive Common Table Expression. Table definition DECLARE @binaryUser AS TABLE ( id integer NOT NULL, joiningDate date NOT NULL, placement char(1) NOT NULL, pId integer NOT NULL, cId integer NOT NULL, referBy integer NOT NULL ); Data INSERT @...


12

If you want all ancestors and all descendants, you can combine the two queries in one. Use the two CTEs and then a simple UNION: WITH RECURSIVE -- descendants rec_d (id, name) AS ( SELECT tree.id, tree.name FROM tree WHERE name = 'Father' UNION ALL SELECT tree.id, tree.name FROM rec_d, tree where tree.parent_id = rec_d.id ...


12

Randi Vertongen's answer correctly addresses how you can get the plan you want with the parameterized version of the query. This answer supplements that by addressing the title of the question in case you are interested in the details. SQL Server rewrites tail-recursive common table expressions (CTEs) as iteration. Everything from the Lazy Index Spool down ...


11

An array representing the path from the root up to the leaf should achieve the desired sort order: WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL LIMIT 10 ) UNION ALL SELECT r.depth + 1, r.path || n.node, n.* FROM node_rec r JOIN nodes n ON n.parent = ...


10

If your queries have a common shape, you might be able to add the required maxrecursion hint using one or more plan guides. There can be a knack to getting them right. If you add specific query details to your question, we might be able to work that out for you. Typically, you would trace the SQL actually hitting the server, or obtain a parameterized form ...


9

Since this is a SQL Server 2014 question I might as well add a natively compiled stored procedure version of a "cursor". Source table with some data: create table T ( TheDate datetime primary key ); go insert into T(TheDate) values ('2014-01-01 11:00'), ('2014-01-03 10:00'), ('2014-01-04 09:30'), ('2014-04-01 10:00'), ('2014-05-01 11:00'), ('2014-07-...


9

If you have absolutely have to use a function (a limitation of your ETL tool as you imply), you can specify OPTION as part of a multi-statement table-valued function, eg something like this: CREATE FUNCTION dbo.udf_MyFunction ( @StartID INT ) RETURNS @tv TABLE ( id INT ) AS BEGIN WITH Episodes( xlevel, PersonID, EventID, EpisodeID, StartDT, EndDT ) AS ...


9

Though at the moment I don't have the title of the actual hotfix, the better query plan will be used when enabling the query optimizer hotfixes on your version (SQL Server 2012). Some other methods are: Using OPTION(RECOMPILE) so the filtering happens earlier, on the literal value. On SQL Server 2016 or higher the hotfixes before this version are applied ...


7

First off, you do not want to use char(50). Use varchar(50) or just text. Read more: Any downsides of using data type “text” for storing strings? Assuming the following rules: Basic slugs never end with a dash. Duplicate slugs are suffixed with a dash and a sequential number (-123). Note that all of the following methods are subject to a race conditions: ...


7

One of the key parts of Jack Douglas's solution in Group by array overlapping is the | (pipe) operator used on arrays in the recursive part of the recursive t CTE like this: ... select t.id, a.id, t.clst | a.clst ... This operator concatenates two arrays suppressing duplicate items. The reason that answer cannot be directly applied to your setup is because ...


6

Why does Recursive CTE estimate just 1 row? Cardinality estimation for recursive common table expressions is extremely limited. Under the original cardinality estimation model, the estimate is a simple sum of the cardinality estimates for the anchor and recursive parts. This is equivalent to assuming the recursive part is executed exactly once. In SQL ...


6

There are a few options that I was able to get working. All of the options deal with variations of filter predicates. NOTE: you must disable the Server Audit in order to make changes, and then re-enable it. First, the most generic approach is to filter out all Scalar UDFs. You can do that by using the class_type audit field. The documentation indicates that ...


6

The anchor part of the recursive SQL query produces all 7488 rows. On my machine, that part of the query finishes in under 100 ms. SSMS does not immediately show all 7488 rows in the grid results. It only shows 7480 for me as well. I suspect this happens because the results are sent in packets and the remaining 8 rows aren't enough to fill a packet. The ...


5

Assuming the original table design is somewhat like this: CREATE TABLE dbo.Area ( RowID integer PRIMARY KEY, GroupID integer NOT NULL, ParentID integer NULL, ); Sample data: INSERT dbo.Area (RowID, GroupID, ParentID) VALUES (1, 1, NULL), -- Root (2, 1, 1), (3, 1, 2), (4, 2, NULL), -- Root (5, 2, 4), (6, 2, 5), ...


5

Recursive queries cannot be done with pure SQL when it comes to MySQL. I have written posts on how to use Stored Procedures to accomplish this Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs Dec 10, 2012 : MySQL: Tree-Hierarchical query Jul 15, 2013 : Get top most parent by nth child id? Give them a Try !!! NOTE : This ...


5

Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan. declare @T table ( Eventdate date index IX_Eventdate clustered, Val int ); insert into @T(Eventdate, Val) values ('2012-03-23', 3965), ('2012-03-26', 3979), ('2012-03-27', 3974), ('2012-03-28', 3965), ('2012-03-29', 3967), (...


5

A solution that uses a cursor. (first, some needed tables and variables): -- a table to hold the results DECLARE @cd TABLE ( TheDate datetime PRIMARY KEY, Qualify INT NOT NULL ); -- some variables DECLARE @TheDate DATETIME, @diff INT, @Qualify INT = 0, @PreviousCheckDate DATETIME = '1900-01-01 00:00:00' ; The actual cursor: -- ...


5

You need a Window Function indeed. However LAG is not the right one. SUM(...) OVER(...) is the one you want. See SQL Fiddle. Query: SELECT account_id, activity_date, amount , SUM(amount) OVER(PARTITION BY account_id ORDER BY activity_date) as balance FROM transfers t ORDER BY account_id, activity_date DESC; Output: account_id | activity_date ...


5

A very primitive implementation: It basically divides the problem into two subproblems: First find all the ancestors of the node in question (including the node itself). If the node has no parents, then this would be just itself. Then find the descendants of all those ancestors (including themselves). We may have several nodes in the ancestors result set, ...


5

This is just a (semi) educated guess, and is probably completely wrong. Interesting question, by the way. T-SQL is a declarative language; perhaps a recursive CTE is translated into a cursor-style operation where the results from the left side of the UNION ALL is appended into a temporary table, then the right side of the UNION ALL is applied to the values ...


5

Use a composite foreign key: (tree_id, parent_id) REFERENCES (tree_id, id) You will need to first change the PK or add a UNIQUE constraint on (tree_id, id)


4

I adapted the example at http://www.postgresql.org/docs/8.4/static/queries-with.html to your case: WITH RECURSIVE search_graph(id, depends, depth, path, cycle) AS ( SELECT g.Col1, g.depends, 1, ARRAY[g.Col1], false FROM deps g UNION ALL SELECT g.Col1, g.depends, sg.depth + 1, path || g.Col1, ...


Only top voted, non community-wiki answers of a minimum length are eligible