Skip to content
Advertisement

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

I have 2 tables, the first one has 10 distinct values:

First Table,

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

enter image description here

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
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement