Generate a Random Integer Within a Range: Quick Tip

Posted January 31, 2019 by Jim Knicely, Vertica Principal Solution Architect

The RANDOMINT functions returns one of n integers from 0 through n – 1.

If you need to generate a random integer that falls within a range of integers between X and Y, inclusive, use this simple formula:

FLOOR(RANDOM() * (Y - X + 1) + X) Example: dbadmin=> SELECT FLOOR(RANDOM() * (20 - 10 + 1) + 10) AS random_integer_between_10_and_20, dbadmin-> FLOOR(RANDOM() * (100 - 50 + 1) + 50) AS random_integer_between_50_and_100, dbadmin-> FLOOR(RANDOM() * (1000 – 100 + 1) + 100) AS random_integer_between_100_and_1000; random_integer_between_10_and_20 | random_integer_between_50_and_100 | random_integer_between_100_and_1000 ----------------------------------+-----------------------------------+------------------------------------- 17 | 95 | 920 (1 row) Helpful Links:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOM.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOMINT.htm

Have fun!