How to Order by Parent Then Child

How do I order by parent then child?

From your comment, if you know there are only two levels, there is an easy solution:

select  *
from    @Features feat
order by
        case 
        when ParentID = 0 
        then Feature 
        else    (
                select  Feature 
                from    @Features parent 
                where   parent.FeatureID = feat.ParentID
                ) 
        end
,       case when ParentID = 0 then 1 end desc
,       Feature

1. Sort by the name of the root element: for the root, this is Feature column. For the children, look up the root's name with a subquery.
2. Sort the root on top
3. Sort the children by name

Example at SE Data.

MySql: ORDER BY parent and child

Including sorting children by id:

ORDER BY COALESCE(parent, id), parent IS NOT NULL, id

SQL Fiddle example

Explanation:

• COALESCE(parent, id): First sort by (effectively grouping together) the parent's id.
• parent IS NOT NULL: Put the parent row on top of the group
• id: Finally sort all the children (same parent, and parent is not null)

SQL Order by parent and child

You want your data ordered in alphabetical order and depth first.

A common solution for this is to traverse the structure from the top element, concatenating the path to each item as you go. You can then directly use the path for ordering.

Here is how to do it in MySQL 8.0 with a recursive query

with recursive cte(venueID, venueName, mainVenueID, path, depth) as (
    select v.venueID, v.venueName, cast(null as char(100)), venueName, 0
    from tblAdmVenue v
    where not exists (select 1 from tblAdmVenueLink l where l.subVenueID = v.venueID)
    union all
    select v.venueID, v.venueName, c.venueID, concat(c.path, '/', v.venueName), c.depth + 1
    from cte c
    inner join tblAdmVenueLink l on l.mainVenueID = c.venueID
    inner join tblAdmVenue v on v.venueID = l.subVenueID
)
select * from cte order by path

The anchor of the recursive query selects top nodes (ie rows whose ids do not exist in column subVenueID of the link table). Then, the recursive part follows the relations.

As a bonus, I added a level column that represents the depth of each node, starting at 0 for top nodes.

Demo on DB Fiddle:

venueID | venueName       | mainVenueID | path                       | depth
:------ | :-------------- | :---------- | :------------------------- | ----:
LA47    | Banquet         | null        | Banquet                    |     0
LA43    | Big Room        | null        | Big Room                   |     0
LA45    | Room Daisy      | LA43        | Big Room/Room Daisy        |     1
LA46    | Room Rose       | LA43        | Big Room/Room Rose         |     1
LA48    | Split 1         | LA46        | Big Room/Room Rose/Split 1 |     2
LA44    | Hall            | null        | Hall                       |     0
LA49    | Meeting Room WP | LA44        | Hall/Meeting Room WP       |     1

SQL Server Order first by ParentID, then Child

you can achieve this using CTE

;with cte as
(
select
    ParentAssetID,
    ChildAssetID,
    AssetName,
    cast(row_number()over(partition by ParentAssetID order by AssetName) as varchar(max)) as [path],
    0 as level,
    row_number()over(partition by ParentAssetID order by AssetName) / power(10.0,0) as x
from Assets
where ParentAssetID =956
union all
select
    t.ParentAssetID,
    t.ChildAssetID,
    t.AssetName,
    [path] +'-'+ cast(row_number()over(partition by t.ParentAssetID order by t.AssetName) as varchar(max)),
    level+1,
    x + row_number()over(partition by t.ParentAssetID order by t.AssetName) / power(10.0,level+1)
from
    cte
join Assets t on cte.ChildAssetID = t.ParentAssetID
)

select
    ParentAssetID,
    ChildAssetID,
    AssetName,
    [path],
    x
from cte
order by x

How to Order table records with parent followed by child using SQL Query

You need a recursive query that carries the root ID through all levels, then you can sort the rows by that:

with recursive entries as (
  select id, parent_id, id as root_id, 1 as level
  from the_table
  where parent_id = 0 -- this should be IS NULL
  union all 
  select c.id, c.parent_id, p.root_id, p.level + 1
  from the_table c
    join entries p on p.id = c.parent_id
)
select id, parent_id
from entries
order by root_id, level, id;

Online example: https://rextester.com/YKUJ56922

Mysql order by parent and child

Simplify your order by clause to just what you want the sort order to be:

SELECT 
    parent.category_id as `parent_id`,
    parent.category_group as `parent_group`,
    child.category_id as `child_id`
    child.category_group as `child_group`
FROM mst_category AS parent 
LEFT JOIN mst_category AS child 
    ON child.category_parent = parent.category_id
GROUP BY parent.category_id
ORDER BY parent.category_group, child.category_group

For 3 levels:

SELECT 
    parent.category_id as `parent_id`,
    parent.category_group as `parent_group`,
    child.category_id as `child_id`
    child.category_group as `child_group`,
    child2.category_id as `child2_id`
    child2.category_group as `child2_group`
FROM mst_category AS parent 
LEFT JOIN mst_category AS child 
    ON child.category_parent = parent.category_id
LEFT JOIN mst_category AS child2
    ON child2.category_parent = child.category_id
WHERE parent.category_id = 0
GROUP BY parent.category_id, child.category_id, child2_category_id
ORDER BY parent.category_group, child.category_group, child2.category_group

SQL Order by, parent, child, and sort order

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.

MySQL Sorting by parents and children

Partial solution might be the following query:

SELECT t.title FROM t GROUP BY IF(parent=0,id,parent), 
IF(parent=0,-1,ordering), t.title ORDER BY IF([parent]=0,
id,parent), IF(parent=0,-1,ordering);

Result is like following:

TitleC
TitleCC
TitleA
TitleAA
TitleAB
TitleAC
TitleB
TitleBA
TitleBB

To indent the header of each group, something like report in MS Access might be used.
I've prepared it in MS Access, so there may be difference slightly in MySQL syntax.

MySQL query to order by parent then child places

I think you should put one more field in your table, called level and store in it the level of the node, and then sort your query by level then by parent.

---

Related Topics