Get the immediate parent child relation in a table

I have a table with following value

userid   roleid   ranklvl
123         a       1
456         b       2
789         c       3

I need the output data from the above in the following format :

userid   roleid   ranklvl  parentroleid
123         a       1        null
456         b       2        a
789         c       3        b

Thanks in advance for any guidance on this.

EDIT : I used the while loop approach to achieve this but trying to avoid the while loop.

declare @a table
(
userid int,roleid char(1),ranklvl int,parentroleid char(1) )

insert into @a(userid,roleid,ranklvl) select userid,roleid,ranklvl from Table [where some condition]

declare @maxcount smallint  = (select max(ranklvl) from @a)
declare @counter smallint = 1
declare @parentroleid char(1)

while( @maxcount >  @counter)
BEGIN
    Select @parentroleid  = roleid from @a where ranklvl  = @maxcount - 1
    UPDATE @a SET parentrole = @parentroleid where ranklvl  = @maxcount
    SET @maxcount = @maxcount -1
END

select * from @a

The while loop logic worked if proper sequence of ranklvl is there for a given record set like 1->2->3. But it did not work if data is in following way :

userid   roleid   ranklvl
123         a       1
789         c       3

The following expected result is not coming by the while loop logic.

userid   roleid   ranklvl  parentroleid
123         a       1       null
789         c       3       a

Answer

I think you want a self-join:

select t.*, p.roleid parentroleid
from mytable t
left join mytable p on p.ranklvl = t.ranklvl - 1

If there are gaps in the ordering column, you can enumerate first:

with cte as (
    select t.*, row_number() over(order by ranklvl) rn
    from mytable t
)
select c.*, p.roleid parentroleid
from cte c
left join cte p on p.rn = c.rn - 1

Advertisement

Answer