Generate unique random numbers using SQL

Question

I have some SQL code which generates random numbers using the following technique:

DECLARE @Random1 INT, @Random2 INT, @Random3 INT, @Random4 INT, @Random5 INT, @Random6 INT, @Upper INT, @Lower INT
---- This will create a random number between 1 and 49
SET @Lower = 1 ---- The lowest random number
SET @Upper = 49; ---- The highest random number


with nums as (
    select @lower as n
    union all
    select nums.n+1
    from nums
    where nums.n < @Upper
   ),
   randnums as 
   (select nums.n, ROW_NUMBER() over (order by newid()) as seqnum
    from nums
   )
select @Random1 = MAX(case when rn.seqnum = 1 then rn.n end),
       @Random2 = MAX(case when rn.seqnum = 2 then rn.n end),
       @Random3 = MAX(case when rn.seqnum = 3 then rn.n end),
       @Random4 = MAX(case when rn.seqnum = 4 then rn.n end),
       @Random5 = MAX(case when rn.seqnum = 5 then rn.n end),
       @Random6 = MAX(case when rn.seqnum = 6 then rn.n end)
from randnums rn;

select @Random1, @Random2, @Random3, @Random4, @Random5, @Random6

My question is how random is this number generation? and is there another way to do this which is more "random".

I am using:

Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64)   Aug 22 2012 19:25:47   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

The problem with most solutions is you'll end up with values like this: 14,29,8,14,27,27 I cannot have duplicate numbers!

As in your previous question you could use ORDER BY CRYPT_GEN_RANDOM(8) as that is documented to return a "cryptographic random number" — Martin Smith
– Martin Smith, Commented Nov 9, 2013 at 12:49
You are right, marked previous answer as ticked for your solution and up voted you on here. If you can explain how the code works in an answer here, ill mark you as ticked :) — PriceCheaperton
– PriceCheaperton, Commented Nov 9, 2013 at 13:07
Actually no, as you're method still causes dupes to show up: ` 2,44,1,13,41,44` — PriceCheaperton
– PriceCheaperton, Commented Nov 9, 2013 at 13:08
The answer there doesn't meet the requirement here that there can be no repetition amongst the 6 numbers though. For that you can just take your existing query here and change the reference to newid to use CRYPT_GEN_RANDOM instead. — Martin Smith
– Martin Smith, Commented Nov 9, 2013 at 13:09

Xbox One · Accepted Answer · 2020-07-28 20:26:27Z

16

I guess you could do something like this much simpler and much easier

DECLARE @Upper INT;
DECLARE @Lower INT;
SET @Lower = 1;     /* -- The lowest random number */
SET @Upper = 49;    /* -- The highest random number */
    
    
SELECT @Lower + CONVERT(INT, (@Upper-@Lower+1)*RAND());

For getting a random number without repetition, this will do the job

WITH CTE 
AS
(
    SELECT  randomNumber, COUNT(1) countOfRandomNumber
    FROM (
    SELECT ABS(CAST(NEWID() AS binary(6)) %49) + 1 randomNumber
    FROM sysobjects
    ) sample
    GROUP BY randomNumber
)
SELECT TOP 5 randomNumber
FROM CTE
ORDER BY newid()

To set the highest limit, you can replace 49 with your highest limit number.

edited Jul 28, 2020 at 20:26

Xbox One

3134 silver badges15 bronze badges

answered Nov 9, 2013 at 12:30

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

PriceCheaperton Over a year ago

Well no, as i need to make sure the same number doesn't appear in random 1-6

PriceCheaperton Over a year ago

The problem with this is that you'll end up with values like this: 14,29,8,14,27,27 I cannot have duplicate numbers!

M.Ali Over a year ago

@PriceCheaperton Have a look I have updated my answer, hopefully this will do the job

Martin Smith Over a year ago

I don't see that this is any "more random" than what they already have. It still relies on the random properties of newid.

Martin Smith Over a year ago

Simply assigning a random number to the numbers 1-49 and taking the top 6 (as they have in the OP) is fine. I don't see this adds any more randomness. My doubt is about the degree of randomness supplied by newid when compared to an inbuilt dedicated function such as CRYPT_GEN_RANDOM

|

Ajay · Accepted Answer · 2019-06-25 12:42:08Z

0

For Laravel:

 public function generatUniqueId()
    {
        $rand = rand(10000, 99999);
        $itemId = $rand;
        while (true) {
            if (!BookItem::whereBookItemId($itemId)->exists()) {
                break;
            }
            $itemId = rand(10000, 99999);
        }

        return $itemId;
    }

answered Jun 25, 2019 at 12:42

Ajay

8889 silver badges17 bronze badges

Comments

thx0125 · Accepted Answer · 2021-11-05 03:29:22Z

Create a list of random numbers. For this example I did 100, could be more, could be less (but no less than your limit)
Use row_number() function to detect duplicates

Once you delete the duplicates, select top 6 number in your list

 with RandomNumbers as
 (
     select id = 1, number = round(((56 - 1 -1) * RAND(CHECKSUM(NEWID())) + 1), 0),
     orderid = round(((56 - 1 -1) * RAND(CHECKSUM(NEWID())) + 1), 0)
     union all
     select id + 1, round(((56 - 1 -1) * RAND(CHECKSUM(NEWID())) + 1), 0), round(((56 - 1 -1) * RAND(CHECKSUM(NEWID())) + 1), 0) 
     from RandomNumbers where id < 100
 ), 
 NoDuplicates as
 (
     select number, id = row_number() over (partition by number orderid by order) from
     (
         select numeber, order, repeat = row_number() over (partition by numeber orderid by order)
         from RandomNumbers 
     ) a
     where repeat = 1
 )

select * from NoDuplicates where id <= 6

Amir Keshavarz · Accepted Answer · 2013-11-09 20:59:20Z

-2

You can use Rand() function .

select CEILING(RAND() *<max of random numbers))

answered Nov 9, 2013 at 20:59

Amir Keshavarz

3,1181 gold badge22 silver badges26 bronze badges

Collectives™ on Stack Overflow

Generate unique random numbers using SQL

4 Answers 4

8 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related