Agreed, it's a strange request that I've never come across.
That said, of the solutions posted, Ashish Gilhotra's is the fastest, but does not (for me at least) return the "correct" results as listed in the challenge. I could not quite get my head around the replace(count(...)-1,0,4)
bit, which is using a text function to update a numeric value. If the dataset includes 10 items, should we really be replacing the answer with 14?
This is basically the same code, but tidied up so that it works.
selecta.code,a.aname, x.bname, x.cname
from@a a
left join(
selectisnull(b.code, c.code) as code, b.bname, c.cname
from(
selectb.code, (select count(*)+1 from @b-2 x where x.code = b.code and x.bname < b.bname) as rank, b.bname
from@b-2 b
)b
full join(
selectc.code, (select count(*)+1 from @C x where x.code = c.code and x.cname < c.cname) as rank, c.cname
from@C c
) c
onc.code = b.code
andc.rank = b.rank
) x
onx.code = a.code