Sorting in recursive query

Question

In another question I asked i had found a solution but ordering only works if the ID’s are growing:

USE TESTE
GO

WITH Niveis AS (
                -- Membro âncora
                SELECT Id, IdPai, convert(varchar(1000), Nome) as Nome,
                0 AS Nivel -- nível 0
                FROM TABELA1
                WHERE IdPai IS NULL

                UNION ALL

                -- Filhos
                SELECT T1.Id, T1.IdPai, convert(varchar(1000), Niveis.Nome + ' - ' + T1.Nome) as Nome,
                Nivel+1
                FROM TABELA1 T1
                INNER JOIN Niveis ON T1.IdPai = Niveis.Id
                )
SELECT Id, IdPai, Nome
FROM Niveis
ORDER BY Id, IdPai

How could I do when ID’s are out of ascending order on levels?

Answer 1

First of all, you need to have a grouping criterion. I’m going to invent one. For example, GrupoId.

Having this, just select by GrupoId and sort at the end of the query as below:

USE TESTE
GO

WITH Niveis AS (
                -- Membro âncora
                SELECT Id, GrupoId, IdPai, convert(varchar(1000), Nome) as Nome,
                0 AS Nivel -- nível 0
                FROM TABELA1
                WHERE IdPai IS NULL

                UNION ALL

                -- Filhos
                SELECT T1.Id, T1.GrupoId, T1.IdPai, convert(varchar(1000), Niveis.Nome + ' - ' + T1.Nome) as Nome,
                Nivel+1
                FROM TABELA1 T1
                INNER JOIN Niveis ON T1.IdPai = Niveis.Id
            )
SELECT GrupoId, Id, IdPai, Nome
FROM Niveis
ORDER BY GrupoId, Id, IdPai

Answer 2

I got a solution that met my needs. Thank you Gypsy, your help was of great value. Follow the code below if anyone has the same question.

USE TESTE
GO

WITH Niveis AS (
                -- Membro âncora
                SELECT Id, IdPai, convert(varchar(1000), Nome) as Nome,
                0 AS Nivel,
                RANK()  OVER (ORDER BY Id, IdPai) AS Ordem
                FROM TABELA1
                WHERE IdPai IS NULL

                UNION ALL

                -- Filhos
                SELECT T1.Id, T1.IdPai, convert(varchar(1000), Niveis.Nome + ' - ' + T1.Nome) as Nome,
                Nivel+1,
                Ordem
                FROM TABELA1 T1
                INNER JOIN Niveis ON T1.IdPai = Niveis.Id
            )
SELECT Id, IdPai,REPLICATE('     ' , Nivel) + Nome AS Nome, Nome
FROM Niveis
ORDER BY Ordem