In SQLite, you can use the random() function to generate a pseudo-random number.

This is great, but the value returned is between -9223372036854775808 and +9223372036854775807.

What if you need a random number between 0 and 10? Or say, 1 and 100?

Fortunately you can do this by combining random() with abs() and the modulo operator.

Random Number Between 0 and 10

You can use the following code to generate a pseudo-random number between 0 and 10.

SELECT abs(random() % 10);

Here’s an example of selecting multiple random values.

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

Result:

R1          R2          R3        
----------  ----------  ----------
2           8           5         

Increase the Range

Of course, you can change the value to anything you want (assuming it’s within the random() range of possible values).

Here it is again with the range increased to between 0 and 100.

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

Result:

R1          R2          R3        
----------  ----------  ----------
76          60          85        

Random Number Between 1 and 10

If you don’t want zero to be part of the possible outcomes, you can use the following method.

The following code generates a pseudo-random number between 1 and 10.

SELECT abs(random()) % (10 - 1) + 1;

Here’s an example of selecting multiple random values.

SELECT 
  abs(random()) % (10 - 1) + 1 AS R1,
  abs(random()) % (10 - 1) + 1 AS R2,
  abs(random()) % (10 - 1) + 1 AS R3;

Result:

R1          R2          R3        
----------  ----------  ----------
2           1           8