SQL PUZZLE | The RANDOM Id PUZZLE | SQL Interview Question

Well one of may favourite question for hiring ARCHITECTs. In this puzzle you have to 2 tables named Table11 and Table12. Both the tables are not related to each other. What we have to do is that for each value of Id(Table11) we need a random value of Id(Table12). For more details please see the sample input and expected output.

Sample Input

Table11

Table12

Expected Output

Note – RandomValue column values may be different each time we execute the solution.

Script – DDL and INSERT Sample Data

--

CREATE TABLE Table11
(
	Id INT
)
GO

INSERT INTO Table11 VALUES
(1),(2),(3),(4)
GO

CREATE TABLE Table12
(
	Id INT
)
GO

INSERT INTO Table12 VALUES
(101),(102),(103),(104),(105),(106)
GO

SELECT * FROM Table11 a
GO

SELECT * FROM Table12 a
GO

--

SOLUTION – 1

--

SELECT x.Id, y.Id RandomValue FROM 
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY NEWID()) rnk FROM Table11 a
)x 
INNER JOIN 
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY NEWID()) rnk FROM Table12 a
)y ON x.rnk = y.rnk

--

Output – 1

--

Id          RandomValue
----------- -----------
2           102
4           106
1           103
3           101

(4 rows affected)

--

SOLUTION – 2

--

SELECT a.id , x.Id RandomValue FROM Table11 a
CROSS APPLY
(
	SELECT TOP 1 b.Id
	FROM Table12 b
	WHERE a.Id IS NOT NULL
	ORDER BY NEWID()
)x

--

Output – 2

--

id          RandomValue
----------- -----------
1           103
2           104
3           106
4           101

(4 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com