SQL SELECT Parent-Child with SORT (...again)

Question

UPDATED: I have a simple, one level parent child relation table, with following columns:

ID_Asset| Parent_ID_Asset | ProductTitle

I need output grouped by Parent followed by children, and also sorted by Parent and Children Name. My attempts in the fiddle.

See here for details: https://rextester.com/PPCHG20007

Desired order:

9   8   NULL  ADONIS Server
7   16  8     ADONIS Designer
8   20  8     ADONIS Portal Module “Control & Release” Package XS

Parent first, than children, while ProductTitle ordered alphabetically.

Thanx all for hints so far.

Answer 1

I would do conditional ordering instead :

select t.*
from table t
order by (case when parent_id is null then id else parent_id end), ProductTitle;

I am assuming you need to sort the data based on parent-child relation.

Answer 2

As far as I understand, you need to sort on the name of the root products, and in between them to show the sub-products, ordered by name, and in between them to show their sub-products, etc.

I guess you are using a recursive cte. You can define a "hierarchy sorting" helper which is a padded number in the current level, and for each level deep, add a suffix with the padded number in the current level, etc.

Something like that:

declare @Products table(ID int, Parent_ID int, ProductTitle varchar(100))
insert into @Products values
(1,    NULL,        'ADONIS'),
(2,    NULL,        'BACARAT'),
(3,    1,           'Portal Module'),
(4,    1,           'Alhambra'),
(5,    NULL,        'ZULU'),
(6,    2,           'Omega')


; with cte as (
select ID, Parent_ID, ProductTitle, FORMAT(ROW_NUMBER() over(order by ProductTitle), '0000') as SortingHelper
from @Products
where Parent_ID is null
union all
select p.ID, p.Parent_ID, p.ProductTitle, cte.SortingHelper + '.' + FORMAT(ROW_NUMBER() over(order by p.ProductTitle), '0000') as SortingHelper
from @Products p
inner join cte on cte.ID = p.Parent_ID
)

select ID, Parent_ID, ProductTitle
from cte
order by SortingHelper

Answer 3

I think this is the ordering you are looking for. This joins each row to its parent (if it exists). Then if there is a TP (parent) record then that is the parent title, ID etc, otherwise the current record must be the parent. I've shown the parent name in the query results for clarity. Then it sorts by

• Parent name (so parents and children are together, but in parent name order)
• Parent ID (in case two or more parents have the same name/title, this will keep children with the correct parent)
• A flag which is 0 for parents, 1 for children, so the parent comes first
• The current record name, which will sort children by name/title order

Code is

Select T.*, 
    isnull(TP.ProductTitle, T.ProductTitle) as ParentName  -- This is the parent name, shown for reference
from test T
    left outer join Test TP on TP.ID_Asset = T.Parent_ID_Asset  --This is the parent record, if it exists
ORDER BY isnull(TP.ProductTitle, T.ProductTitle),  --ParentName sort
    isnull(TP.ID_Asset, T.ID_Asset),  --if two parents have the same title, this makes sure they group with their correct children
    Case when T.Parent_ID_Asset is null then 0 else 1 end,  --this makes sure the parent comes before the child
    T.ProductTitle  --Child Sort