In some circumstances, the following may perform better than the ROW_NUMBER version.

SELECT t2.*

FROM #table1 t

CROSS APPLY (

SELECT TOP ( t.qty ) *

FROM #table2 t2

WHERE t2.forename = t.name

ORDER BY t2.cost DESC

) t2

The requirements are that the number of records in the outer table are relatively small, the number of records per forename in the inner table are relatively large, and there exists an index on table2 forename and cost (preferably desc) that includes the surname. (NOTE: that is a single index that meets all three criteria. It does not do any good to have separate indexes on forename and cost regardless of whether either includes the other field or the surname.)

Drew