Tuesday, March 10, 2015

Order by Parent Child in Sql server

declare @ParentChild table (ChildID int, ParentID int, Name varchar(50))
insert @ParentChild values
 (1, null, 'First Parent'),
 (2, null, 'Second Parent'), 
 (3, 2, 'Second Child 1'), 
 (4, 1, 'First Child 1'), 
 (5, 2, 'Second Child 2'), 
 (6, 1, 'First Child 2');

    select  *
    from    @ParentChild feat
    order by
            case 
            when ParentID is null 
            then Name
            else    (
                    select  Name

                    from    @ParentChild parent 
                    where   parent.ChildID = feat.ParentID
                    ) 
            end
    ,       case when ParentID is null then 1 end desc
    ,       Name