Tag Info

New answers tagged

2

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 = ...


3

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), ...


0

There seems to be a bit of negativity here towards CTE's. My understanding of a CTE is that it's basically a kind of adhoc view. SQL is both a declarative and a set based language. CTE's are a great way of declaring a set! Not being able to index a CTE is actually a good thing because you don't need to! It's really a kind of syntactic sugar to make the ...



Top 50 recent answers are included