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

limit 1 1 in sql

where can i learn sql for free

sql update replace

subquery in postgresql

sql multiple left joins

sql select from database

sql on delete cascade

sql get day of year

add minutes to date sql server

sql not in statement

You can also search these topics using sql random function, sql server random string, sql generate random string, sql random string, tsql random number, postgresql order by random, sql random select, 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

Related Links

create database sql server

sql where string contains

sql except vs not in

count function in sql returns the number of

first day of the week sql

my sql not exists

create view sql

syntax for alter table in sql

datediff sql seconds

t sql any operator

You can also search these topics using 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.