CODEWITHSUNDEEP
sqliterandom
Fischer
Fischer

Reputation: 1523

Generating only positive random numbers in SQLite

In SQLite

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

You can get an random with maximum value n with Select random() % n ;

But that still generates negative numbers. How can I generate only positive random numbers in SQLite ?

Upvotes: 17

Views: 17260

Answers (4)

NullUserException
NullUserException

Reputation: 85468

Use the ABS() (absolute value) function:

SELECT ABS(RANDOM() % N)

Note that:

If X is the integer -9223372036854775808 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two's complement value.

Upvotes: 27

Sanju
Sanju

Reputation: 213

For generating positive(including zero) random number with only upper bound, use

SELECT ABS(RANDOM() % N)

For generating positive and non-zero random number, use

SELECT ABS(RANDOM()) % (HIGH - LOW) + LOW

HIGH - represents upper bound

LOW - represents lower bound.

Upvotes: 18

Pete Cacioppi
Pete Cacioppi

Reputation: 920

SELECT ABS(RANDOM() % N)

and

SELECT ABS(RANDOM()) % N

both tend to give the desired result.

To my eye, the second is more readable. First we pseudo randomly select an integer, then we apply ABS() insure it's non-negative, then we modulo this by N to select the appropriate range.

However, the first method is more reliable. The second method has a very low, but non zero, risk of throwing an integer overflow, as noted by NullUserException.

Upvotes: 1

Guffa
Guffa

Reputation: 700372

Your method random() % n for getting a random number within a certain range is flawed, as it will give an uneven distribution. The number zero will come up twice as often as any other number.

Incidentally, using ABS to make the numbers positive is flawed in the exact opposite way, making the number zero turn up half as often as any other number, so they cancel each other out.

abs(random() % n)

Upvotes: 8

Related Questions