I have 2 tables, the first one has 10 distinct values:
each GlobalPnID has many values on the second table, I want to join 2 tables and select one random value of PortionKey of the second table that match the condition and move to the next GlobalPnID
SELECT TOP 10 gpnp.PortionKey, tt.GlobalPnID from #TempTable tt LEFT JOIN [dbo].[GlobalPartNumberPortions] gpnp ON gpnp.GlobalPnId = tt.GlobalPnID -- tt is the first table -- gpnp is the second
Advertisement
Answer
SELECT TT.GlobalPnID,X.PortionKey FROM #TempTable AS TT CROSS APPLY ( SELECT TOP 1 R.PortionKey FROM [dbo].[GlobalPartNumberPortions] AS R WHERE R.GlobalPnId=TT.GlobalPnID ORDER BY R.PortionID )X