{"id":983,"date":"2016-07-19T08:20:48","date_gmt":"2016-07-19T08:20:48","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=983"},"modified":"2025-03-16T21:05:09","modified_gmt":"2025-03-17T04:05:09","slug":"sql-rand","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-math-functions\/sql-rand\/","title":{"rendered":"SQL RAND Function"},"content":{"rendered":"
\n
\"SQL<\/figure>\n<\/div>\n\n\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL RAND<\/code> function to generate a pseudo-random number at runtime.<\/p>\n\n\n\n

Introduction to SQL RAND function #<\/a><\/h2>\n\n\n\n

The RAND<\/code> function generates a pseudo-random floating-point number between 0 and 1 (inclusive). The following illustrates the syntax of the RAND<\/code> number:<\/p>\n\n\n

RAND(seed);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The RAND<\/code> function accepts an optional seed argument with the integer data type. If you call the RAND<\/code> function with the same seed value within a session, the function will return exactly the same value. To get different random numbers, you must specify different seed values, or use different sessions.<\/p>\n\n\n\n
If you omit the seed argument, the RAND<\/code> function returns a floating-point random number between the function calls.<\/p>\n\n\n\n
Note that some database systems e.g., PostgreSQL, provides a function named RANDOM<\/code> that is equivalent to the RAND<\/code> function.<\/p>\n\n\n\n
SQL RAND function examples #<\/a><\/h2>\n\n\n\n
The following example shows how to generate a random number between 0 and 1.<\/p>\n\n\n
SELECT<\/span> RAND<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you execute the statement multiple times, you will see different random numbers.<\/p>\n\n\n\n
If you execute the following statement multiple times, it yields exactly the same value because the RAND<\/code> function uses the same seed value.<\/p>\n\n\n
SELECT<\/span> RAND<\/span>(100<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Generate a random integer between a range #<\/a><\/h2>\n\n\n\n
To generate a random integer R in the range (n,m), n <= R < m, you use the RAND<\/code> function in conjunction with the ROUND<\/code><\/a> function as follows:<\/p>\n\n\n
ROUND(n + RAND() * (m \u2212 n))<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, to get a random number between 1 and 100, you use the following statement.<\/p>\n\n\n
SELECT<\/span> ROUND<\/span>(1<\/span> + (RAND<\/span>() * 99<\/span>)) AS<\/span> RAND_1_100;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Querying rows in random order #<\/a><\/h2>\n\n\n\n
You can use the RAND<\/code> function in the ORDER BY<\/code><\/a> clause to retrieve rows in random order as follows:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> table_name\nORDER<\/span> BY<\/span> RAND<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To select a random sample from a set of rows, you add the LIMIT<\/code> clause to the above statement. The following statement retrieves N random rows in a table.<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> table_name\nORDER<\/span> BY<\/span> RAND<\/span>()\nLIMIT<\/span> N;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"employees_table\"<\/figure>\n\n\n\n
The following statement retrieves 5 random employees.<\/p>\n\n\n
SELECT<\/span>\n  employee_id,\n  first_name,\n  last_name\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  RAND<\/span> ()\nLIMIT<\/span>\n  5<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 employee_id | first_name | last_name\n-------------+------------+-----------<\/span>\n         113 | Luis       | Popp\n         204 | Hermann    | Baer\n         179 | Charles    | Johnson\n         110 | John       | Chen\n         111 | Ismael     | Sciarra\n(5 rows)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you should use this way for getting random rows from a table with a small set of data. For a big table, it would be slow.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the SQL RAND<\/code> function to generate a\u00a0pseudo-random number in queries at runtime.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try it<\/a><\/p>\n\n\n
Let’s see the employees<\/code> table in the sample database<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n