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:
- 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 - Make parent appear on top of each group
parent_ques_id
- nulls sort before values in ASC order as default (in SQL Server) - 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
How to Convert Milliseconds to Time(Hh:Mm:Ss) in Oracle
Retrieve Varbinary Value as Base64 in Mssql
Sql - Select Parent and Child Records in an Order
How to Select Multiple Values in One Field MySQL
How to Have a Default Parameter for a MySQL Stored Procedure
Mysql Error 1449: the User Specified as a Definer Does Not Exist
Selecting a Single Record from the Same Multiple Rows from a Single Table
Error Code 1292 Incorrect Date Value MySQL
How to Extract Year from Hire_Date in My SQL
How to Include Results of SQL Count If Count=0
How to Convert This SQL Select to Linq Query
How to Calculate Age (In Years) Based on Date of Birth and Getdate()
Formatting Numbers by Padding With Leading Zeros in SQL Server
Duplicate Rows When Joining Tables
How to Select Rows With Only Numeric Characters in Oracle SQL
Comparing Two Columns Using SQL to Make Sure the Same Set of Values Are Present in Both
How to Enforce Case Sensitive Table and Column Names in MySQL