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.
Related Links
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 |
Related Links