SQL RAND() Function


The SQL RAND() function is used to generate a floating-point number or whole integer number by randomly.

The SQL RAND() function will generate unique(maximum time) number or duplicate(some time) number at every execution or function calling.

The SQL RAND() function will generate number between 0.0(inclusive) to 1.0(exclusive). The generated number will be in floating-point numbers and by default it will not generate whole integer number. We can generate whole interger number (from any number or within range between 2 numbers) using some formula's with rand() function.

It can be used in any valid SQL SELECT statement as well in SQL where clause.


Search Keys

  • sql random function
  • rand function
  • sql random
  • sql server random string
  • sql generate random string
  • sql random string
  • postgresql random
  • tsql random number
  • postgresql order by random
  • sql random select
  • ms sql random
  • sql server rand
  • sql order by random

SQL RAND() Syntax

The below syntax is used to generate a random number (floating-point number).

For SQL SERVER / MYSQL


SELECT RAND(seed_number)

Note: The parameter "seed_number" is "OPTIONAL" and used to generate same number in the result.

For MS ACCESS


SELECT RND(seed_number)

The below syntax is used to generate a whole integer number (any numbers or within range of between 2 integer numbers).

For SQL SERVER / MYSQL


SELECT FLOOR((RAND() * (X - Y)) + Y)

For MS ACCESS


SELECT INT((RND() * (X - Y) + 1) + Y)

Note: Where "X" is the highest number and "Y" is the smallest number that you want to generate a random number between "X" and "Y".


SQL RAND() Example - Using Expression Or Formula

The following SQL SELECT statement return a random floating-point number between "0.0" and "1.0".


SELECT RAND()

The result of above query is:

Expr
0.38475628374

SQL RAND() Function More Example

Input Value Result
RAND() 0.21342256
RAND() 0.82364526
RAND() 0.46338755
RAND(8) 0.33345321
RAND(8) 0.33345321
RAND(8) 0.33345321
FLOOR((RAND() * (20 - 10)) + 10) 13
FLOOR((RAND() * (20 - 10)) + 10) 18
FLOOR((RAND() * (20 - 10)) + 10) 11
FLOOR((RAND() * (50 - 30)) + 30) 42
FLOOR((RAND() * (50 - 30)) + 30) 39
FLOOR((RAND() * (50 - 30)) + 30) 31

Sample Database Table - Author

AuthorId AuthorName Gender DomainName
1 Suresh Babu Male Hacking
2 Siva Kumar Male Database
3 Azagu Bala Haris Male Programming
4 Varshini Kutty Female Database
5 Bala Murugan Male Games
6 Dharan Kumar Male Games

SQL RAND() Example

There are 6 records in the "Author" table. So the maximum value will be "6" and the minimum value will be "1" in the condition.

The following SQL statement will fetch randomly any one record at a time from the "Author" table:


SELECT * FROM Author
WHERE AuthorID = FLOOR((RAND() * (6 - 1)) + 1)

Note: The RAND() function will generate any random number between "1" to "6". There will be only one record in the result.

The result of above query is for "First Time" run:

AuthorId AuthorName Gender DomainName
3 Azagu Bala Haris Male Programming

The result of above query is for "Second Time" run:

AuthorId AuthorName Gender DomainName
1 Suresh Babu Male Hacking

The result of above query is for "Third Time" run:

AuthorId AuthorName Gender DomainName
6 Dharan Kumar Male Games

Search Keys

  • sql random query
  • t sql generate random string
  • generate random number sql server
  • random sql data generator
  • sql random data generator
  • rand function sql
  • sql select random number
  • db2 random function
  • random number generator sql server
  • select random sql server
  • number functions in sql
  • sql server build numbers
  • sql string to number