How to get mysql random integer range?

Question

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

Answer 1

It works fine when I try it.

Does products.* include a column named "timer"? And you're fetching it into an associative array (keyed by column name) in your application? It could be the timer column is overwriting your timer expression in the associative array. I've seen that happen before.

Change "AS timer" to "AS randomtimer," to be sure.

Answer 2

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)?

Answer 3

This is working for me. Your mysql version maybe?

SELECT id, (FLOOR( 1 + RAND( ) *60 )) AS timer
FROM users
LIMIT 0 , 30

Answer 4

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 INT) + 1) AS timer

Answer 5

I ended up generating the random numbers in PHP, in an array the same length as the query results then sorting that and echoing it in a loop with the query results.