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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT ROUND  6 - 3   RAND     0

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In general   select rand     upper- lower   lower    For your question   select rand    6-3  3     lt     select rand   3 3

User · Answer

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

User · Answer

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   This is slower than the NEWID   method  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

[sql] Generate random int value from 3 to 6

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008