Thank's mate! Perfect solution!

Resolved 🙂

It should be

OVER (ORDER BY ParentID, OC)

not

OVER (ORDER BY OC, ParentID)

Thx again!

There is a problem... I modified data as follows:

DECLARE @FirstTable TABLE

( ParentID int , ParentName char(4) )

insert @FirstTable

...

Perfect!!!!

Thank you so much!

P.S.

I'm sorry, you are absolutely right about example data. Next time i'll use create table and insert!

Thank you so much Peter and Paul. I tested Peter solution and it works perfectly.

I wanted to know if hierarchyid works in SQL 2005, because i get "Type hierarchyid is...

Hello Peter!

Thx for you response.

I think you misunderstood the problem. There is no group by "name", because all the records are unique. It means "name" can not be repeated twice,...

Unfortunately it takes a lot of time, because db is more than 200GB, and DB is in live production, so i can't allow to do it...

During new year days...

Thank you so much Mark! Very elegant solution... I was also thinking for double join, but was falling in the same trap as Roger, thinking that it will not work,...