Generate random int value from 3 to 6

Question

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:

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

Answer 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 - thanks to jiraiya for providing the visual presentation)

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)

Answer 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.

Answer 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.

Answer 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

Answer 5

Nice and simple, from Pinal Dave's site:

http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 3 ---- The lowest random number
SET @Upper = 7 ---- One more than the highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

(I did make a slight change to the @Upper- to include the upper number, added 1.)

Answer 6

Simply:

DECLARE @MIN INT=3; --We define minimum value, it can be generated.
DECLARE @MAX INT=6; --We define maximum value, it can be generated.

SELECT @MIN+FLOOR((@MAX-@MIN+1)*RAND(CONVERT(VARBINARY,NEWID()))); --And then this T-SQL snippet generates an integer between minimum and maximum integer values.

You can change and edit this code for your needs.

Answer 7

In general:

select rand()*(@upper-@lower)+@lower;

For your question:

select rand()*(6-3)+3;

<=>

select rand()*3+3;

Answer 8

SELECT ROUND((6 - 3 * RAND()), 0)

Answer 9

Lamak's answer as a function:

-- Create RANDBETWEEN function
-- Usage: SELECT dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID())))
CREATE FUNCTION dbo.RANDBETWEEN(@minval TINYINT, @maxval TINYINT, @random NUMERIC(18,10))
RETURNS TINYINT
AS
BEGIN
  RETURN (SELECT CAST(((@maxval + 1) - @minval) * @random + @minval AS TINYINT))
END
GO

Answer 10

DECLARE @min INT = 3;
DECLARE @max INT = 6;
SELECT @min + ROUND(RAND() * (@max - @min), 0);

Step by step

DECLARE @min INT = 3;
DECLARE @max INT = 6;

DECLARE @rand DECIMAL(19,4) = RAND();
DECLARE @difference INT = @max - @min;
DECLARE @chunk INT = ROUND(@rand * @difference, 0);
DECLARE @result INT = @min + @chunk; 
SELECT @result;

Note that a user-defined function thus not allow the use of RAND(). A workaround for this (source: http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/) is to create a view first.

CREATE VIEW [dbo].[vw_RandomSeed]
AS
SELECT        RAND() AS seed

and then create the random function

CREATE FUNCTION udf_RandomNumberBetween
(
    @min INT,
    @max INT
)
RETURNS INT
AS
BEGIN
    RETURN @min + ROUND((SELECT TOP 1 seed FROM vw_RandomSeed) * (@max - @min), 0);
END