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

Id
1
2
3
4

Table12

Id
101
102
103
104
105
106

Expected Output

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

Id RandomValue
1 106
2 105
3 103
4 101

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 🙂

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.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

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: