making sure random numbers are not the same using SQL

Question

I have the following which will return 100k rows in the database which should find me 6 random values. The problem is in the Lotto you cannot have multiple same values. I have noticed that some values are showing up the same, eg.. `21-21-34-3-14-46` Its clear that `21` and `21` are NOT valid numbers in a Lotto ticket. So my question is how do i check that no numbers are the same when generating a random value? Here is my code: ``` SET NOCOUNT ON DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=500000) BEGIN --Do Stuff 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 SELECT @Random1 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SELECT @Random2 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SELECT @Random3 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)…

Accepted Answer

When I think of random numbers with no duplicates, I think "permutation". So, the idea is to generate the set of possible values, order them randomly, and then choose the first six values as your set:

```
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;
```

making sure random numbers are not the same using SQL

Solution

Problem