This is part of a series of quick tips and tricks I have accumulated over the years, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.
The trick to generate a random number for each row is to use the RAND() function, multiply it by the maximum number you need and then cast the number as INT.
The code below show how to create a number between 0 and 24 and another example of how to create a number between 15 and 25.
The calculation of the random int for each row is not very efficient, so you should consider using a more efficient method for generating the random numbers if you need it in production code.
You have to do something slightly different to generate a range of int on the SQL Server
CREATE OR REPLACE TEMPORARY TABLE names (Name VARCHAR(50)); INSERT INTO names (Name) VALUES ('Joe'), ('Bob'), ('Anne'), ('Jane'); -- Generate random ints between 0 and 24 SELECT Name, CAST(MOD(RAND() * 100000, 25) AS INT) AS `Random Int` FROM names; -- Generate random ints between 15 and 25 SELECT Name, CAST(MOD(RAND() * 100000, (25-15)) AS INT) + 15 AS `Random Int` FROM names;