Generate Unique Random Numbers Using SQL

Generate unique random numbers using SQL

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.

How to generate a unique and random number for a primary key in sql

I have gone with the below solution and it works fine with millions of records too.. Thank you all for your answers.

      Calendar cal = Calendar.getInstance();
      long currentTime = cal.getTimeInMillis();
      long Max = 9999999999999L;
      long Min = 1000000000000L;
    long range = Math.abs((long) (Math.random() * (Max - Min)) + Min);
    long id = Math.addExact(currentTime, range);
    String uniqueID = createUniqueID(id);
    boolean isRepeated = urlShortenerRepository.existsByShortUrlKey(uniqueID);
    while (isRepeated) {
        range = Math.abs((long) (Math.random() * (Max - Min)) + Min);
        id = Math.addExact(currentTime, range);
        uniqueID = createUniqueID(id);
        isRepeated = urlShortenerRepository.existsByShortUrlKey(uniqueID);
    }

SQL: how to get a unique random number with no collision in a UDF

One of classic approaches:

• fill in table/array with lots of random values
• number the rows in it with identity/index
• refer it's rows with independently secuentially incremented value

e.g.

CREATE TABLE BulkOfRealRandoms (id int identity(1,1), rand_value BINARY(8)

Fill it with something like db of md5's. I mean do this as one-time job. Don't do anytnihg like this in OLTP scenarios, inside transactions. As many other said - your udf is a performance killer.

CREATE TABLE RandomReceiver (self_id int, rand_value_id int)

You may use IDENTITY or SEQUENCE to generate rand_value_id. That's how you'll get random values with no collusions fast and confidently enough.

If you are trying to hide growth of some tables in your db with assigning random public ids instead of sequential ones, then you should probably invent an algorythm for producing such ids which probably might be of string datatype. Qualitative randomization is not a trivial task. And if this is your scenario (producing of public order ids or similar) then random numbers won't be really comfortable and useful.

sql - Generate a unique random value for each row

Select x.*, @i := @i + 1 randid from my_table, (Select @i:=4000) vars order by rand();

How do you generate multiple unique random numbers within a range in mysql

Welcome to S/O. So, RAND() returns a random floating point number between 0 and 1, such as .28932353289090. So, if you want to get a given value such as from 0-10, just multiply the RAND() * 10. It will still be floating point, so if you wanted an integer of it, just wrap that with a cast, such as cast( rand() * 10 as signed ) to get an INTEGER value result. But casting this can give you a 10 due to implied rounding such as getting a .95 or greater will round UP to the next digit.

Next, you want the breakdowns going for the low, medium and high range of 2 - 32, 33-65, 66-98. So your gaps are not perfectly balanced, but I think what you are trying to do is break a possible 0-98 (99 possible values) into 3 groups. Why those specific, I dont know, but can give you a good start to see how you might get and adjust as you see fit.

Your low group is actually at most 32, but starting a MINIMUM of 2. So, start with your maximum RANGE which is 30. So, based on the casting I exampled above,

cast( 30 * rand() as signed) 

will give you a value of 0-30. Now, just add 2 to it since that is your low range, thus a final of

2 + cast( 30 * rand() as signed)

Now, apply similar logic to your medium and high.

33-65 = range = 33-33 = 0 (low), 65-33 = 32 (high) randomness + a fixed 33

33 + cast( 32 * rand() as signed) 

So, if the rand() return 0, then you are at 33 + 0 = 33, your low value for the mid range. If the rand() returns the .95 or better, * 32 will give you your high randomness of 32 + your base of 33 = 65, the high end of you mid range.

Same for your high end

66-98 = 66-66 = 0 (low), 98-66 = 32 (high) + fixed 66.

66 + cast( 32 * rand() as signed) 

Finally, your steps between x, where x = 1, you are in the below 100 range. x=2 is 100 starting range, x=3 = 200 starting range. So this is simply tacking on (x-1) * 100 to whatever your underlying random such as

(( x-1 ) * 100 ) + ( 33 + cast( 32 * rand() as signed ))

So, you were getting close, but I think this resolves it for you. By using FLOOR() will always chop-off the rounding, but applying the cast as signed will allow the full range. Your choice on means to get rid of rounding.

Generating unique random number with condition Snowflake

It's not possible to guarantee uniqueness from a random number generator function unless you use some kind of calculated part. For example:

create or replace table testX as
select  CONCAT('TEST-' , to_char(seq4(),'0000'),  
uniform(100000, 990000, random())) c 
from table(generator(rowcount => 1000));

How to generate a unique random number when insert in MySQL?

albeit my sql skills are a bit rusty, I think you might want to create a function using the RAND function.

CREATE PROCEDURE GetRandomValue()
BEGIN

  DECLARE newUrlId INT DEFAULT 0;

  WHILE (
    newUrlId = 0 
    OR IF EXISTS(SELECT 1 FROM yourTable WHERE url_id = newUrlId)
  )
  DO
    SET newUrlId = SELECT FLOOR(RAND() * 999999)
  END WHILE

  RETURN newUrlId
END

Then again, why creating such a fuss while you could use other ways to create "bigger random numbers"

for example:

function createBiggerNumber(id) {
  return (id * constants.MySecretMultiplyValue) + constants.MySecretAddedValue;
}

function extractIdFromBiggerNumber(number) {
  return (number - constants.MySecretAddedValue) / constants.MySecretMultiplyValue
}

---

Related Topics