Sql - Select Parent and Child Records in an Order

SQL - select parent and child records in an order

You can do this using a trick with coalesce:

select *
from t
order by coalesce(parent_ques_id, ques_id), parent_ques_id, ques_id

Sorting logic would be:

  1. Sort all parent-child relations one after another (97, 101, 115) coalesce(parent_ques_id, ques_id) - assign a parent it's own id (instead of null) to be sorted with his children
  2. Make parent appear on top of each group parent_ques_id - nulls sort before values in ASC order as default (in SQL Server)
  3. Sort children by their id ques_id

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

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.

Select Parent and Child Records

Use a CTE to build the company hierarchy, then join this back to the Employees table:

with CompanyHierarchy as
(
select Id
from Company
where Id = 1
union all
select c.Id
from Company c
inner join CompanyHierarchy ch on c.ParentCompanyId = ch.Id
)
select e.*
from CompanyHierarchy ch
inner join Employees e on ch.Id = e.CompanyId

SQL Fiddle with demo.

You can also substitute a CompanyId variable into the anchor portion of the CTE if you want to parameterize the statement:

with CompanyHierarchy as
(
select Id
from Company
where Id = @CompanyId
union all
select c.Id
from Company c
inner join CompanyHierarchy ch on c.ParentCompanyId = ch.Id
)
select e.*
from CompanyHierarchy ch
inner join Employees e on ch.Id = e.CompanyId

SQL Fiddle with demo, now with added hierarchy levels.

select parent and parent's child from the same table and order asc

You can try this way to get rows as you want.

SELECT c.title,ca.title,concat(c.id,'-',ca.id) as sort_column FROM 

cat c
join cat as ca on ca.ana_cat = c.id order by sort_column

Parent child hierarchy with order by on name

It is fairly straight forward, really:

SELECT
category.*,
-- bring parent and its children together
CASE WHEN parent.Id IS NULL THEN category.Name ELSE parent.Name END AS sort1,
-- move parent to top followed by its children
CASE WHEN parent.Id IS NULL THEN NULL ELSE category.Name END AS sort2
FROM category
LEFT JOIN category AS parent ON category.parentId = parent.Id
ORDER BY sort1, sort2

Output:

+------+------+----------+-------+-------+
| Id | Name | parentId | sort1 | sort2 |
+------+------+----------+-------+-------+
| 5 | ABC | 0 | ABC | NULL |
| 3 | PQR | 5 | ABC | PQR |
| 2 | XYZ | 5 | ABC | XYZ |
| 4 | EFG | 0 | EFG | NULL |
| 1 | STU | 0 | STU | NULL |
| 7 | DEF | 1 | STU | DEF |
| 6 | HIJ | 1 | STU | HIJ |
+------+------+----------+-------+-------+

Note that I have placed the sort calculations inside the SELECT clause in order to explain how it works.



Related Topics



Leave a reply



Submit