How to get mysql random integer range?
MysqlRandomMysql Problem Overview
I am trying to generate a random integer for each row I select between 1 and 60 as timer.
SELECT downloads.date, products.*, (FLOOR(1 + RAND() * 60)) AS timer
I have searched and keep coming up to this FLOOR function as how to select a random integer in a range. This is giving me a 1 for every row. What am I missing?
I am on mysql 5.0.75
Heres the rest of the query I belive it might be a nesting issue
SELECT *
FROM (
SELECT downloads.date, products.*, FLOOR(1 + (RAND() * 60)) AS randomtimer,
(
SELECT COUNT( * )
FROM distros
WHERE distros.product_id = products.product_id
) AS distro_count,
(SELECT COUNT(*) FROM downloads WHERE downloads.product_id = products.product_id) AS true_downloads
FROM downloads
INNER JOIN products ON downloads.product_id = downloads.product_id
) AS count_table
WHERE count_table.distro_count > 0
AND count_table.active = 1
ORDER BY count_table.randomtimer , count_table.date DESC LIMIT 10
Mysql Solutions
Solution 1 - Mysql
This is working for me. Your mysql version maybe?
SELECT id, (FLOOR( 1 + RAND( ) *60 )) AS timer
FROM users
LIMIT 0 , 30
Solution 2 - Mysql
The output of the RAND
function will always be a value between 0 and 1.
Try this:
SELECT downloads.date, products.*, (CAST(RAND() * 60 AS UNSIGNED) + 1) AS timer
Solution 3 - Mysql
I'm running your query and it does give me a random number for each row.... maybe has something to do with the name of the random (timer)?
Solution 4 - Mysql
Old question, but always actual problem.
Here a way to create a MySQL function random_integer()
based on manual :
CREATE FUNCTION random_integer(value_minimum INT, value_maximum INT)
RETURNS INT
COMMENT 'Gets a random integer between value_minimum and value_maximum, bounds included'
RETURN FLOOR(value_minimum + RAND() * (value_maximum - value_minimum + 1));
SELECT ALL random_integer(1, 60) AS timer;
Solution 5 - Mysql
You can increase the number multiplied by the number of records in the table.
SELECT id,
(FLOOR( (SELECT MIN(id) FROM your_table ) + RAND( ) * 1000000 ) ) AS timer
FROM your_table
LIMIT 0 , 30