How to get second parent with recursive query in Common Table

sql, sql-server

Solution

It took me a while, but here it is :)

with tmp as (
  select unitId, parentId, unitName, 0 as iteration
  from t
  where unitId = 7
  union all
  select parent.unitId, parent.parentId, parent.unitName, child.iteration + 1
  from tmp child
  join t parent on child.parentId = parent.unitId
  where parent.parentId != 0
)
select top 1 unitId, parentId, unitName from tmp
order by iteration desc

Here is also a fiddle to play with.

Problem

I am using SQL Server 2008. I have a table like this: ``` UnitId ParentId UnitName --------------------------- 1 0 FirstUnit 2 1 SecondUnit One 3 1 SecondUnit Two 4 3 B 5 2 C 6 4 D 7 6 E 8 5 F ``` I want to get second parent of the record. For example: If I choose unit id that equal to 8, It will bring unit id is equal to 2 to me. It needs to be SecondUnit One. or If I choose unit id that equal to 7, It will bring unit id is equal to 3 to me. It needs to be SecondUnit Two. How can I write a SQL query this way?

Original source