sql - How to Select one Value for each row after Joining of 2 Tables

Question

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 

question from:https://stackoverflow.com/questions/65880709/how-to-select-one-value-for-each-row-after-joining-of-2-tables

Answer 1

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