Generate random int value from 3 to 6

Is it possible in Microsoft SQL Server generate random int value from Min to Max (3-9 example, 15-99 e.t.c)

I know, i can generate from 0 to Max, but how to increase Min border?

This query generate random value from 1 to 6. Need to change it from 3 to 6.

SELECT table_name, 1.0 + floor(6 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

Added 5 sec later:

Stupid question, sorry...

SELECT table_name, 3.0 + floor(4 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

Solution 1:

A helpful editor added the 'Select' before each statement but the point of this item is that it can generate unique keys for each row in a return, not just one item (For that I would us the Rand() function). For example: Select top 100 Rand(),* from tblExample

Would return the same random value for all 100 rows.

While: Select top 100 ABS(CHECKSUM(NEWID()) % 10),* from tblexample

Would return a different random value between 0 and 9 on each row in the return. So while the select makes it easier to copy and paste, you can copy the logic into a select statement if that is what is required.

This generates a random number between 0-9

SELECT ABS(CHECKSUM(NEWID()) % 10)

1 through 6

SELECT ABS(CHECKSUM(NEWID()) % 6) + 1

3 through 6

SELECT ABS(CHECKSUM(NEWID()) % 4) + 3

Dynamic (Based on Eilert Hjelmeseths Comment, updated to fix bug( + to -))

SELECT ABS(CHECKSUM(NEWID()) % (@max - @min - 1)) + @min

Updated based on comments:

• NEWID generates random string (for each row in return)
• CHECKSUM takes value of string and creates number
• modulus (%) divides by that number and returns the remainder (meaning max value is one less than the number you use)
• ABS changes negative results to positive
• then add one to the result to eliminate 0 results (to simulate a dice roll)

Solution 2:

I see you have added an answer to your question in SQL Server 2008 you can also do

SELECT 3 + CRYPT_GEN_RANDOM(1) % 4 /*Random number between 3 and 6*/ 
FROM ...

A couple of disadvantages of this method are

1. This is slower than the NEWID() method
2. Even though it is evaluated once per row the query optimiser does not realise this which can lead to odd results.

but just thought I'd add it as another option.

Solution 3:

You can do this:

DECLARE @maxval TINYINT, @minval TINYINT
select @maxval=24,@minval=5

SELECT CAST(((@maxval + 1) - @minval) *
    RAND(CHECKSUM(NEWID())) + @minval AS TINYINT)

And that was taken directly from this link, I don't really know how to give proper credit for this answer.

Solution 4:

Here is the simple and single line of code

For this use the SQL Inbuild RAND() function.

Here is the formula to generate random number between two number (RETURN INT Range)

Here a is your First Number (Min) and b is the Second Number (Max) in Range

SELECT FLOOR(RAND()*(b-a)+a)

Note: You can use CAST or CONVERT function as well to get INT range number.

( CAST(RAND()*(25-10)+10 AS INT) )

Example:

SELECT FLOOR(RAND()*(25-10)+10);

Here is the formula to generate random number between two number (RETURN DECIMAL Range)

SELECT RAND()*(b-a)+a;

Example:

SELECT RAND()*(25-10)+10;

More details check this: https://www.techonthenet.com/sql_server/functions/rand.php