SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE elbat
             (parentassetid integer,
              childassetid integer,
              assetname nvarchar(8));
INSERT INTO elbat
            (parentassetid,
             childassetid,
             assetname)
            VALUES
(84,            2,            'abc'),
(35,            1,            'cdf'),
(956,           35,           'PARENT35'),
(84,            1,            'ghi'),
(956,           3,            'PARENT3'),
(35,            3,            'jkl'),
(956,           84,           'PARENT84'),
(3,             5,            'mno');
8 rows affected

SELECT t1.*
       FROM elbat t1
       ORDER BY CASE
                  WHEN NOT EXISTS (SELECT *
                                          FROM elbat t2
                                          WHERE t2.childassetid = t1.parentassetid) THEN
                    t1.childassetid
                  ELSE
                    t1.parentassetid
                END,
                CASE
                  WHEN NOT EXISTS (SELECT *
                                          FROM elbat t2
                                          WHERE t2.childassetid = t1.parentassetid) THEN
                    0
                  ELSE
                    1
                END,
                t1.childassetid;


parentassetid
childassetid
assetname


956
3
PARENT3

3
5
mno

956
35
PARENT35

35
1
cdf

35
3
jkl

956
84
PARENT84

84
1
ghi

84
2
abc

parentassetid	childassetid	assetname
956	3	PARENT3
3	5	mno
956	35	PARENT35
35	1	cdf
35	3	jkl
956	84	PARENT84
84	1	ghi
84	2	abc