Thanks for the reply.

The reason for the filter, I don't want to return the whole table. If I add a few more records, and only want Snow White and the punks:

DROP TABLE tempdb.dbo.People;

CREATE TABLE tempdb.dbo.[People]

(

[Number] [nvarchar](50) NULL

,[Name] [nvarchar](75) NULL

,[ParentNumber] [nvarchar](50) NULL

);

INSERT INTO tempdb.dbo.People

( Number, Name, ParentNumber )

VALUES ( '111', 'Snow White', NULL )

, ( '222', 'Sleepy', '111' )

, ( '333', 'Dopey', '111' )

, ( '444', 'Doc', '111' )

, ( '555', 'Doc Jr', '444' )

, ( '666', 'Doc Jr Jr', '555' )

, ( '1111', 'Uncle Scrooge', NULL )

, ( '2222', 'Huey', '1111' )

, ( '3333', 'Duey', '1111' )

, ( '4444', 'Luey', '1111' )

;

WITH ctePeople

AS ( SELECT Number

,Name

,CAST (NULL AS NVARCHAR(50)) AS ParentNumber

,1 AS CustLevel

FROM tempdb.dbo.People

WHERE ParentNumber IS NULL AND Number = '111'

UNION ALL

SELECT bic.Number

,bic.Name

,cte.Number AS ParentNumber

,cte.CustLevel + 1

FROM tempdb.dbo.People bic

INNER JOIN ctePeople cte ON bic.ParentNumber = cte.Number

WHERE bic.ParentNumber IS NOT NULL

)

SELECT *

FROM ctePeople cte

--WHERE Number = '111'

-- OR ParentNumber = '111'

ORDER BY CustLevel;

I think I answered my own question by filtering the top root select in the cte.

This gives me the Snow White hierarchy and leaves the ducks out.