CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1447679
user1447679

Reputation: 3230

SQL Order by, parent, child, and sort order

I have the following query:

SELECT
 p.CategoryID
,p.Category_Name
,p.IsParent
,p.ParentID
,p.Sort_Order
,p.Active
,p.CategoryID AS sequence

FROM tbl_Category p
WHERE p.IsParent = 1

    UNION

 SELECT
  c.CategoryID
 ,' - ' + c.Category_Name AS Category_Name
 ,c.IsParent
 ,c.ParentID
 ,c.Sort_Order
 ,c.Active
 ,c.ParentID as sequence
   FROM tbl_Category c
   WHERE c.IsParent = 0

   ORDER BY sequence, ParentID, Sort_Order

This results in:

Parent
  - child
Parent
  - child
  - child

etc.

What I'm finding difficult is getting the results to obey the Sort_Order so that the Parents are in proper sort order, and the children under those parents are in proper sort order. Right now it's sorting based on the ID of the Parent category.

Not sure about advanced grouping or how to handle it.

Upvotes: 2

Views: 5076

Answers (3)

Dennis Xavier
Dennis Xavier

Reputation: 109

After a lot of searches I have come with a solution for the problems like this It can resolve your Issue. Here I have another sort order column which selects the same sort order of its parent records. Using this Secondary SortOrder Order the record first, then group the child records under Parent 

CASE WHEN ParentID = 0 THEN CategoryID ELSE ParentID END,  
CASE WHEN ParentID = 0 THEN 0 ELSE Primary_Sort_Order END,

this is used to group the child fields under Its Parent.

The Final Solution like

SELECT * FROM
    (
     SELECT p.CategoryID
       , p.Category_Name
       , p.IsParent
       , p.ParentID
       , p.Active
       , p.Sort_Order AS Primary_Sort_Order
       , CASE WHEN p.IsParent = 0 THEN (SELECT Sort_Order FROM tbl_Category WHERE 
     CategoryID = p.ParentID) ELSE p.Sort_Order END AS Secondary_Sort_Order
      FROM tbl_Category p
    ) x
    ORDER BY Secondary_Sort_Order,
            CASE WHEN ParentID = 0 THEN CategoryID ELSE ParentID END,

            CASE WHEN ParentID = 0 THEN 0 ELSE Primary_Sort_Order END,
         Primary_Sort_Order ASC

Hope It helps....!

Upvotes: 1

Gidil
Gidil

Reputation: 4137

If I understand the problem correctly, you want to order the data by 2 different indexes:
1. For the parents
2. For the children

Assuming this I would try changing the ORDER BY clause to something like this:

ORDER BY 
CASE WHEN p.IsParent = 1 THEN CATEGORY NAME ELSE PARENT NAME END, 
p.IsParent

This is a bit rough since I don't know what the data looks like.
If you add some sample data, I'll try to make a working example.

===Edit===

If I assume the following (simplified) data:

╔═══════════════════════╗
║ ID Parent_ID IsParent ║
╠═══════════════════════╣
║ 1   0        1        ║
║ 2   5        0        ║
║ 3   5        0        ║
║ 4   1        0        ║
║ 5   0        1        ║
║ 6   5        0        ║
╚═══════════════════════╝

I would expect the following output order: 

-1
  -4
-5
  -2
  -3
  -6

In order to do this you want to ORDER BY column ID when IsParent = 1 and by column ParentID when IsParent = 0
You can use a secondary order to order the children by there IDs.

Hope this clears it up a bit for you.

Upvotes: 0

Sybeus
Sybeus

Reputation: 1189

This is how I would do it, assuming the tree that the parent-child relationship represents is only two levels deep.

SELECT *
FROM
(
  SELECT p.CategoryID
       , p.Category_Name
       , p.IsParent
       , p.ParentID
       , p.Active
       , p.Sort_Order as Primary_Sort_Order
       , NULL as Secondary_Sort_Order
  FROM tbl_Category p
  WHERE p.IsParent = 1
  UNION
  SELECT c.CategoryID
       , ' - ' + c.Category_Name AS Category_Name
       , c.IsParent
       , c.ParentID
       , c.Active
       , a.Sort_Order as Primary_Sort_Order
       , c.Sort_Order as Secondary_Sort_Order
  FROM tbl_Category c
  JOIN tbl_Category a on c.ParentID = a.CategoryID
  WHERE c.IsParent = 0
  AND a.IsParent = 1
) x
ORDER BY Primary_Sort_Order ASC
       , (CASE WHEN Secondary_Sort_Order IS NULL THEN 0 ELSE 1 END) ASC
       , Secondary_Sort_Order ASC

Primary_Sort_Order orders the parents and its children as a group first. Then within the primary group, enforce NULL values of Secondary_Sort_Order to come first, and afterwards order by regular non-NULL values of Secondary_Sort_Order.

Upvotes: 2

Related Questions