Sort in recursive query

Question

Sort in recursive query

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

In another question I asked I had found a solution but the sort order works only 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 can I do when the ID's are out of order in the levels?

sql-server tsql query

asked by anonymous 27.02.2015 / 16:06

2 answers

1

First, you need to have a grouping criterion. I'll come up with one. For example, GrupoId .

Having this, just select by GrupoId and order 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

27.02.2015 / 17:38

Mysql - Reading data in a string Click the button without using GUI

score 0 · Accepted Answer

I got a solution that met my needs. Thanks Gypsy, your help was of great value. Follow the code below if someone 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