Order by (parent, child group) and values alphabetically

Question

Found an interesting problem here.

ResultID ParentID ValueX
--------------------------
1         0        GrandParent
2         1        Parent1
3         1        Parent2
4         2        Child1
5         2        Child2
6         3        Child3
7         3        Child4

I want my query result set to look like this:

ResultID ParentID ValueX
--------------------------
1         0        GrandParent
2         1        Parent1
4         2        Child1
5         2        Child2
3         1        Parent2
6         3        Child3
7         3        Child4

If the ParentID is 0, it means that it's a major category. If the ParentID is greater than 0, it means it's a minor category, a child of the parent.

So the parents need to be ordered A-Z and the children need to be ordered A-Z as a group and within this group the values must be considered for sorting alphabetically. This is not limited to 3 levels and can go until 10.

Can you help me get this ordered correctly?

WITH resultset (resultid, parentid, valuex) AS (
SELECT 1,0,'Grandparent' FROM dual UNION ALL
SELECT 2,1,'Parent1' FROM dual UNION ALL
SELECT 3,1,'Parent2' FROM dual UNION ALL
SELECT 4,2,'Child1' FROM dual UNION ALL
SELECT 5,2,'Child2' FROM dual UNION ALL
SELECT 6,3,'Child3' FROM dual UNION ALL
SELECT 7,3,'Child4' FROM dual )
SELECT ResultID , ParentID, ValueX
FROM resultset
ORDER BY ????

score 1 · Answer 1 · answered Jun 06 '16 at 16:47

You can create a path list using a recursive cte, the following is SQL Server syntax, but oracle should be similar:

;with cte AS (SELECT ResultID,ParentID,ValueX, List = CAST(ResultID AS VARCHAR(MAX))
              FROM #Table1
              WHERE ParentID = 0
              UNION ALL
              SELECT a.ResultID,a.ParentID,a.ValueX, List = b.List + ','+CAST(a.ResultID AS VARCHAR(MAX))
              FROM #Table1 a
              JOIN cte b
                ON a.ParentID = b.ResultID
                )
SELECT *
FROM cte
ORDER BY List

Output:

ResultID    ParentID    ValueX      List
1           0           GrandParent 1
2           1           Parent1     1,2
4           2           Child1      1,2,4
5           2           Child2      1,2,5
3           1           Parent2     1,3
6           3           Child3      1,3,6
7           3           Child4      1,3,7

You can of course exclude List from the SELECT list and still order by it.

@realspirituals Ah, thought it was oracle. Well without recursion I don't think there's a clever way to do this. on the db side that wouldn't be arduous, Self-joins 10x. Without one of those, all you know from a row is insufficient to get the order you want. Hopefully someone else has a clever idea! — Hart CO, Jun 07 '16 at 12:24

score 1 · Accepted Answer · edited Jun 14 '16 at 10:52

You can do this by self-joining to generate a list of values from the hierarchy to order on, as shown in the code below. I've expanded to add an extra level of hierarchy to the original example to show how this would work. Clearly this depends on knowing the number of hierarchy levels to generate a reasonable plan (you could always do 10 levels, for example, but that will be a big performance hit if you only have 3 levels of hierarchy in your example).

With further thought I imagine you could use an EXEC statement to generate the SQL needed for a particular hierarchy level, rather than generating manually as below (which will have some optimisations as e.g. we know if an entry does not have anything at L3 it won't have anything at L4 either).

WITH resultset (resultid, parentid, valuex) AS (
SELECT 1,0,'Grandparent' UNION ALL
SELECT 2,1,'Parent1' UNION ALL
SELECT 3,1,'Parent2' UNION ALL
SELECT 4,2,'Child1' UNION ALL
SELECT 5,2,'Child2' UNION ALL
SELECT 6,3,'Child3' UNION ALL
SELECT 7,3,'Child4' UNION ALL
SELECT 8,4,'Child1_Child1' UNION ALL
SELECT 9,7,'Child4_Child1' UNION ALL
SELECT 10,6,'Child3_Child1')
SELECT l1.resultid , l1.parentid, l1.valuex, l2.resultid l2val, l3.resultid l3val,l4.resultid l4val,

-- rewrite COALESCE so clearer how this matches the pattern below
CASE WHEN l4.resultid IS NULL THEN
CASE WHEN l3.resultid IS NULL THEN
CASE WHEN l2.resultid IS NULL THEN l1.valuex 
ELSE l2.valuex END
ELSE l3.valuex END
ELSE l4.valuex END o1,

CASE WHEN l4.resultid IS NULL THEN 
CASE WHEN l3.resultid IS NULL THEN 
CASE WHEN l2.resultid IS NULL THEN '' 
ELSE l1.valuex END
ELSE COALESCE (l2.valuex, l1.valuex, '') END
ELSE COALESCE (l3.valuex, l2.valuex, l1.valuex, '') END o2,

CASE WHEN l3.resultid IS NULL THEN ''
WHEN l4.valuex IS NULL THEN l1.valuex
ELSE l2.valuex END o3,

CASE WHEN l2.valuex IS NULL THEN '' 
WHEN l4.valuex IS NULL THEN '' ELSE l1.valuex END o4

FROM resultset l1
left join resultset l2 on l1.parentid = l2.resultid
left join resultset l3 on l2.parentid = l3.resultid
left join resultset l4 on l3.parentid = l4.resultid
ORDER BY o1, o2, o3, o4

Results (apologies for bad formatting):

RESULTID    PARENTID    VALUEX          L2VAL   L3VAL   L4VAL   O1          O2      O3      O4
    1       0           Grandparent     (null)  (null)  (null)  Grandparent         
    2       1           Parent1         1       (null)  (null)  Grandparent Parent1     
    4       2           Child1          2       1       (null)  Grandparent Parent1 Child1  
    8       4           Child1_Child1   4       2       1       Grandparent Parent1 Child1  Child1_Child1
    5       2           Child2          2       1       (null)  Grandparent Parent1 Child2  
    3       1           Parent2         1       (null)  (null)  Grandparent Parent2     
    6       3           Child3          3       1       (null)  Grandparent Parent2 Child3  
    10      6           Child3_Child1   6       3       1       Grandparent Parent2 Child3  Child3_Child1
    7       3           Child4          3       1       (null)  Grandparent Parent2 Child4  
    9       7           Child4_Child1   7       3       1       Grandparent Parent2 Child4  Child4_Child1

nice hack. But this works only for 4 levels. I need something generic for any number of levels — Srini V, Jun 07 '16 at 12:13
The original question says this can go up to 10 levels, so you could hard-code that with a 10-way self-join. Failing that, it would be more elegant to do this using EXEC to generate the query you want (and limit it to the number of levels of hierarchy required each time, as mentioned above, for performance reasons) — mc110, Jun 07 '16 at 13:32

Order by (parent, child group) and values alphabetically

2 Answers2