Generate a random number in the range 1 - 10

Breaking it down: From float to int

Converting Float Numbers to Integers

If floating-point numbers give you seasickness and you'd rather rock the boat with integers, rely on the TRUNC function at your aid:

PostgreSQL:

SELECT TRUNC(RANDOM() * 9 + 1); -- You shall not pass... the decimal point!


This shaves off any decimal affiliations from the random number, rounding it down to a sturdy integer.

Proving the randomness with generate_series()

Feeling skeptic about randomness? Let's put it to the test with our friend generate_series():

SELECT min(random_value), max(random_value)
FROM (
  SELECT TRUNC(RANDOM() * 9 + 1) as random_value
  FROM generate_series(1,1000)
) as numbers; -- Because seeing is believing, right?


These queries will show the min and max values from a hefty sample of a thousand, validating your faith in randomness.

Deep dive into SQL randomness

Leverage Modulo for desired ranges

With the mighty modulo operator (%), you can also fine-tune your range:

SELECT (TRUNC(RANDOM() * 10) % 10) + 1; -- The Modulo Operator — your unsuspected BFF in SQL.


You can see it sectarianly ensures the values to lie within a selfish 1 to 10 range.

Mind the parts: SQL’s dialects

SQL dialects love to play hide and seek with you. Make sure you play along by remembering the different syntax like SQL Server's RAND() versus PostgreSQL's RANDOM().

Randomness at scale: Large Datasets

When running on large datasets, it's awesome to measure efficiency — because time is precious (and we've got more random numbers to generate!).

Making the range game fair

The Inclusive Ceiling

If you need to ensure the range is really '10' inclusive and doesn't just loiter around it, play with CEILING:

SELECT CEILING(RANDOM() * 10); -- Ceiling — the bouncer that ensures nobody gets left out.


Rounds up to the neighbor who's a whole number — now that's being inclusive.

Your Custom Range

If your range is unique, as unique as you, here's another method to define them:

SQL Server or MySQL:

SELECT CAST((RAND() * (b-a+1)) + a AS INT); -- Because custom ranges need love too.


For a to b range, replace a with your desired start number and b with your end number.