The SQLite random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

A pseudo-random number is a number that appears to be random, but is not truely random. A pseudo-random number is not truely random because its value was generated by a known seed. However, a pseudo-random number will appear to be random if the user has no knowledge of the seed or algorithm that created it.

Therefore, pseudo-random numbers are often considered good enough for many applications.

Example

Here’s an example of generating a pseudo-random number with the random() function.

SELECT random();

Result:

-882536775989953141

Positive Values Only

You can combine random() with abs() to generate only positive values.

SELECT abs(random());

In this case, if random() generates a negative value, abs() will return the absolute value instead.

To demonstrate how abs() works, here’s what happens if I pass the (negative) random number from the first example to the abs() function:

SELECT abs(-882536775989953141);

Result:

882536775989953141

So if we pass random() to abs(), and random() generates a negative value, abs() will return that value as a positive value.

Value Between 0 and 100

Here’s an example of generating a positive number between 0 and 100.

SELECT abs(random() % 100);

Here’s an example of selecting multiple random values.

SELECT 
  abs(random() % 100) AS R1,
  abs(random() % 100) AS R2,
  abs(random() % 100) AS R3;

Result:

R1          R2          R3        
----------  ----------  ----------
17          79          90        

Return Random Rows

You can use random() in an ORDER BY clause of a database query to return random rows.

Here’s an example.

SELECT * FROM Artist 
ORDER BY random() LIMIT 5;

Result:

ArtistId    Name                                    
----------  ----------------------------------------
131         Smashing Pumpkins                       
127         Red Hot Chili Peppers                   
169         Black Eyed Peas                         
60          Santana Feat. Dave Matthews             
240         Gustav Mahler                           

And here’s what I get if I run it again:

ArtistId    Name                                    
----------  ----------------------------------------
234         Orchestra of The Age of Enlightenment   
126         Raul Seixas                             
76          Creedence Clearwater Revival            
182         Nega Gizza                              
63          Santana Feat. Lauryn Hill & Cee-Lo      

If you have a large table, you might want to modify your query to something like this:

SELECT * FROM Artist 
WHERE ArtistId IN 
  (SELECT ArtistId FROM Artist ORDER BY random() LIMIT 5);

Result:

ArtistId    Name                                    
----------  ----------------------------------------
45          Sandra De Sá                            
105         Men At Work                             
110         Nirvana                                 
205         Chris Cornell                           
267         Göteborgs Symfoniker & Neeme Järvi