Ordering SQL Select Query Results

Question

Suppose I have a table called Events with data similar to the following:

  ID | Name | ParentEvent ----+----------------+----------------- 0 | Happy Event | NULL 1 | Sad Event | NULL 2 |Very Happy Event| 0 3 | Very Sad Event | 1 4 | Happiest Event | 2 5 |Unpleasant Event| 1 

How can I query this table to get the results returned so that

• Events that have non-zero ParentEvent appear immediately after the event with ID , collecting ParentEvent
• Events with a ParentEvent value of ParentEvent have a depth of 0. If an event has a depth of n, any event that is a parent has a depth of n + 1.
• As long as the results satisfy the previous two conditions, the order in which the results are displayed does not matter.

In the above table, I would like to get a result set that looks like

  ID | Name | ParentEvent | Depth | ----+----------------+--------------+--------+ 0 | Happy Event | NULL | 0 | 2 |Very Happy Event| 0 | 1 | 4 | Happiest Event | 2 | 2 | 1 | Sad Event | NULL | 0 | 3 | Very Sad Event | 1 | 1 | 5 |Unpleasant Event| 1 | 1 | 

How can I build a SQL query to get this result set? I use T-SQL, but if you can do it to any taste of SQL, please continue and answer.

Answer 1

The following queries return the exact result set that you requested. All this works by calculating the full path to the root nodes and using some technique to arrange this path.

SQL Server 2008 and later. Here, by converting to a hierarchyid data type, SQL Server handles the order correctly.

 WITH Data AS ( SELECT ID, Name, ParentID, Depth = 0, Ancestry = '/' + Convert(varchar(max), ID) + '/' FROM hierarchy WHERE ParentID IS NULL UNION ALL SELECT H.ID, H.Name, H.ParentID, D.Depth + 1, Ancestry = D.Ancestry + Convert(varchar(max), H.ID) + '/' FROM Data D INNER JOIN hierarchy H ON H.ParentID = D.ID ) SELECT ID, Name, ParentID, Depth FROM Data ORDER BY Convert(hierarchyid, Ancestry); 

SQL Server 2005 and later. We can convert the ID values ​​to a string and lay them out so that they are sorted.

 WITH Data AS ( SELECT ID, Name, ParentID, Depth = 0, Ancestry = Right('0000000000' + Convert(varchar(max), ID), 10) FROM hierarchy WHERE ParentID IS NULL UNION ALL SELECT H.ID, H.Name, H.ParentID, Depth + 1, Ancestry = D.Ancestry + Right('0000000000' + Convert(varchar(max), H.ID), 10) FROM Data D INNER JOIN hierarchy H ON H.ParentID = D.ID ) SELECT ID, Name, ParentID, Depth FROM Data ORDER BY Ancestry; 

We can also use varbinary (otherwise it is the same as the previous request):

 WITH Data AS ( SELECT ID, Name, ParentID, Depth = 0, Ancestry = Convert(varbinary(max), Convert(varbinary(4), ID)) FROM hierarchy WHERE ParentID IS NULL UNION ALL SELECT H.ID, H.Name, H.ParentID, Depth + 1, Ancestry = D.Ancestry + Convert(varbinary(4), H.ID) FROM Data D INNER JOIN hierarchy H ON H.ParentID = D.ID ) SELECT ID, Name, ParentID, Depth FROM Data ORDER BY Ancestry; 

SQL Server 2000 and above, allowing the tree to have a maximum of 800 levels:

 SELECT *, Ancestry = CASE WHEN ParentID IS NULL THEN Convert(varchar(8000), Right('0000000000' + Convert(varchar(10), ID), 10)) ELSE '' END, Depth = 0 INTO #hierarchy FROM hierarchy; WHILE @@RowCount > 0 BEGIN UPDATE H SET H.Ancestry = P.Ancestry + Right('0000000000' + Convert(varchar(8000), H.ID), 10), H.Depth = P.Depth + 1 FROM #hierarchy H INNER JOIN #hierarchy P ON H.ParentID = P.ID WHERE H.Ancestry = '' AND P.Ancestry <> ''; END; SELECT ID, Name, ParentID, Depth FROM #hierarchy ORDER BY Ancestry; DROP TABLE #hierarchy; 

The same varbinary conversion can be performed, allowing deeper up to 2000 levels.

Answer 2

This is just an addition to M.Ali's answer. I understand that the OP said: "As long as the results satisfy the previous two conditions, the order in which the results appear does not matter." However, by adding a column to the query that tracks the hierarchy path, you can display the results in the same way as in the question.

 ;WITH CTE AS ( SELECT ID, NAME, ParentID, 0 as Depth, convert(varbinary(max), convert(varbinary(2), ID)) as ThePath FROM hierarchy WHERE ParentID is null UNION ALL SELECT h.ID, h.NAME, h.ParentID, cte.Depth + 1, cte.ThePath + convert(varbinary(max), convert(varbinary(2), h.ID)) as ThePath FROM hierarchy AS h INNER JOIN CTE as cte ON h.ParentID = cte.ID ) SELECT ID, NAME, ParentID, Depth, ThePath FROM CTE ORDER BY ThePath 

Such results are displayed here.

 ID NAME ParentID Depth ThePath ----------- ------------------------------ ----------- ----------- --------------- 0 Happy Event NULL 0 0x0000 2 Very Happy Event 0 1 0x00000002 4 Happiest Event 2 2 0x000000020004 1 Sad Event NULL 0 0x0001 3 Very Sad Event 1 1 0x00010003 5 Unpleasant Event 1 1 0x00010005 

Answer 3

Test Data

 CREATE TABLE hierarchy (ID INT, NAME NVARCHAR(30), ParentID INT) INSERT INTO hierarchy VALUES (0,'Happy Event' ,NULL), (1,'Sad Event' ,NULL), (2,'Very Happy Event',0), (3,'Very Sad Event' ,1), (4,'Happiest Event' ,2), (5,'Unpleasant Event',1) 

Query (Sql Server 2005+)

 ;WITH ClassHierarchy_CTE (CID, ClassID_Join, Level) AS ( SELECT ID, ID AS Join_Class, 0 FROM hierarchy AS c UNION ALL SELECT cte.CID, h.ParentID, Level + 1 FROM hierarchy AS h INNER JOIN ClassHierarchy_CTE as cte ON h.ID = cte.ClassID_Join ) SELECT CTE.CID AS ID , Hic.NAME AS NAME , Hic.ParentID AS ParentEvent , COUNT(*)-1 AS Depth FROM ClassHierarchy_CTE CTE INNER JOIN hierarchy Hic ON CTE.CID = Hic.ID WHERE ClassID_Join IS NOT NULL GROUP BY CTE.CID, Hic.NAME, Hic.ParentID 

Result set

 ╔════╦══════════════════╦═════════════╦═══════╗ ║ ID ║ NAME ║ ParentEvent ║ Depth ║ ╠════╬══════════════════╬═════════════╬═══════╣ ║ 0 ║ Happy Event ║ NULL ║ 0 ║ ║ 1 ║ Sad Event ║ NULL ║ 0 ║ ║ 2 ║ Very Happy Event ║ 0 ║ 1 ║ ║ 3 ║ Very Sad Event ║ 1 ║ 1 ║ ║ 4 ║ Happiest Event ║ 2 ║ 2 ║ ║ 5 ║ Unpleasant Event ║ 1 ║ 1 ║ ╚════╩══════════════════╩═════════════╩═══════╝