realspirituals realspirituals - 5 months ago 17
SQL Question

Order by (parent, child group) and values alphabetically

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 ????

Answer

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