sql - Possible ways to get the group Last/Max record in a hierarchical query? -

assuming have table one:

create table user_delegates (     [id] int identity(1,1) not null,     [user_from] varchar(10) not null,     [user_to] varchar(10) not null,     constraint [pk_user_delegates] primary key clustered ([id] asc),     constraint [uk_user_delegates] unique ([user_from] asc) ) 

so user has right delegate system access user b. when that, won't able access system anymore - user b have "break" delegation before able use system again...

but consider that, if user b delegates access user c, user c will start impersonating user a, , on.

(i know seems security nightmare - please let's forget that, ok? :-))

also consider records:

insert user_delegates([user_from], [user_to]) values ('anthony', 'john') insert user_delegates([user_from], [user_to]) values ('john', 'john') insert user_delegates([user_from], [user_to]) values ('karl', 'joshua') insert user_delegates([user_from], [user_to]) values ('joshua', 'piotr') insert user_delegates([user_from], [user_to]) values ('piotr', 'hans') 

so need finding last (which means active) delegation each user.

i have come solution i've decided not show here (unless ignores me, possibility). can is long answer, , surely seems using cannon kill flea...

but how that? consider relevant sql server extension available, , notice we're looking answer both elegant , performance...

btw, expected result set:

id          user_from  user_to    ----------- ---------- ---------- 1           anthony    john       2           john       john       3           karl       hans       4           joshua     hans       5           piotr      hans        (5 row(s) affected) 

and in advance!

with    q (user_initial, user_from, user_to, link)         (         select  user_id, user_id, user_id, link            users         union         select  user_initial, q.user_to, ud.user_to, link + 1            q         join    user_delegates ud         on      ud.user_from = q.user_to         ) select  *    (         select  *, row_number() on (partition user_initial order link desc) rn            q         )   rn = 1