tu lo que quieres es ordenar los datos segun el padre...o que aparezcan ordenados como les corresponde, para eso puedes usar lo siguiente:
Código SQL:
Ver originalCREATE TABLE #temp
(
id INT,
codigo VARCHAR(20),
padreid INT
)
INSERT INTO #temp VALUES (1 , '5' , NULL)
INSERT INTO #temp VALUES (2 , '6-2' , 7)
INSERT INTO #temp VALUES (3 ,'6-1' , 7)
INSERT INTO #temp VALUES (4 ,'5-1' , 1)
INSERT INTO #temp VALUES (5 ,'5-1-1' , 4)
INSERT INTO #temp VALUES (6 ,'5-2' , 1)
INSERT INTO #temp VALUES (7 ,'6' , NULL)
SELECT * INTO #temp2 FROM(
SELECT
t1.id,t1.padreid,t1.codigo,t2.padreid AS padre2,t2.id AS id2,t2.codigo AS codigo2
FROM #temp AS t1
LEFT JOIN #temp AS t2 ON (t1.id=t2.padreid)
WHERE t2.padreid IS NOT NULL
) AS completa
SELECT id AS padre ,padreid AS id,codigo FROM (
SELECT *,ROW_NUMBER() OVER(partition BY codigo ORDER BY id) AS rn FROM
(
SELECT id,padreid,codigo FROM #temp2
UNION
SELECT padre2,id2,codigo2 FROM #temp2
) AS query
) query2 WHERE rn=1
y obtienes lo que necesitas :)
saludos!