CODEWITHSUNDEEP
sqlsql-servert-sql
Abdallah Abdulghany
Abdallah Abdulghany

Reputation: 25

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

Upvotes: 0

Views: 448

Answers (2)

Christian Phillips
Christian Phillips

Reputation: 18769

You could use Row_Number with a CTE and set the criteria you want, for example:

DECLARE @TempTable TABLE
  (
     globalpnid INT
  )
DECLARE @GlobalPartNumberPortions TABLE
  (
     portionid  INT,
     portionkey NVARCHAR(10),
     globalpnid INT
  )

INSERT INTO @TempTable
            (globalpnid)
VALUES      (1),(2),(3),(4)

INSERT INTO @GlobalPartNumberPortions
            (portionid,
             portionkey,
             globalpnid)
VALUES      (1,'ABC',1),
            (2,'XYZ',1),
            (3,'AZZ',2),
            (4,'QWE',3),
            (5,'TYU',4);

WITH cteportion
     AS (SELECT portionkey,
                globalpnid,
                rn = Row_number()
                       OVER (
                         partition BY globalpnid
                         ORDER BY RAND(CHECKSUM(NEWID()))))
         FROM   @GlobalPartNumberPortions)
SELECT gpnp.portionkey,
       tt.globalpnid
FROM   @TempTable tt
       LEFT JOIN cteportion gpnp
              ON tt.globalpnid = gpnp.globalpnid
                 AND gpnp.rn = 1

This will partition the second table by the globalpnid ordering on ORDER BY RAND(CHECKSUM(NEWID()))) and you can then use this in the join gpnp.rn = 1. In the example I've included, you'll see that GlobalPnID = 1 will alternate between ABC and XYZ.

Edit: as suggested by @Thorsten Kettner in the comment, you can order by RAND(CHECKSUM(NEWID())))

Upvotes: 1

Sergey
Sergey

Reputation: 5225

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

Upvotes: 2

Related Questions