Hi Chris,

I just chaged the query like this and it worked for me.

SELECT id, value, id1, value1

FROM @t1 t1

CROSS JOIN (

SELECT TOP(SELECT TOP(1) [Rows] = COUNT(*) FROM @t2 GROUP BY id1 ORDER BY COUNT(*) DESC) n FROM (select id from @t1) rc (n)

) x

LEFT JOIN (SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY id1 ORDER BY value1) FROM @t2) t2 ON t2.id1 = t1.id AND t2.rn = x.n

Thanks a lot chris.

Thanks for your help.:-):-):-)