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