sql - 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:

Stupid question, sorry...

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, 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)