SQL Puzzle | Generate Random Numbers Within Limits Puzzle

One of my friend is working in R language and he require some random numbers for an ID column but the random numbers should lie between minimum of ID value and maximum of ID value. And these random numbers should not be in a sequence.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

This is just a sample output, Any random value with in 1 to 7 will work for us.

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE RandomePuzzle
(
	ID INT
)
GO

INSERT INTO RandomePuzzle
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7

GO

--

Rules/Restrictions

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using ID & NEWID

--


SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) ID , ID RandomNumber  FROM RandomePuzzle

--Above solution will work if you have sequential data. Now if there is any gap in the Ids then use below solution

CREATE TABLE #GenerateNumbers
(
	ID BIGINT IDENTITY(1,1) PRIMARY KEY	
)
GO

INSERT INTO #GenerateNumbers DEFAULT VALUES
GO 7

SELECT r.ID , p.RandomNumber FROM RandomePuzzle r
LEFT JOIN
(
	SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) ID , ID RandomNumber  FROM #GenerateNumbers r1
)p 
ON p.ID = r.ID

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com