[SOLVED] WTF random integers

[kevisazombie]

HI all,

 

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?

[laPistola]

try

 

SELECT downloads.date, products.*, FLOOR(1 + (RAND() * 60)) AS timer

 

also i believe this is MySQL 5 so make sure your not using 4

[kevisazombie]

Thanks laPistola, no luck though still getting all 1s I am on mysql 5.0.75

[laPistola]

Your welcome

 

if it not possible to just do

 

<?php
$query = "SELECT downloads.date, products.*, ". rand(0,60) ." AS timer";
$results = mysql_query($query, $db) or die(mysql_error());
?>

 

not sure if it would work with it being as AS but worth a go

 

if not could you not just include the rand(0,60) on the page where you needed it??

[kevisazombie]

The php rand() solution doesn't work. As it is out side of the mysql and i need a random number generated for each row. It just pulls one random number and applys it to all rows.

 

I can't pull the random number outside of the query as I am going to use it in an ORDER BY statment.

 

I think my query might have other issues here is the whole thing

 

SELECT *
FROM (
SELECT downloads.date, products.*,  FLOOR(1 + (RAND() * 60)) AS timer, 
(
SELECT COUNT( * )
FROM distros
WHERE distros.product_id = products.product_id
AND distros.compatibility_id = 1
) 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.timer , count_table.date DESC";
LIMIT :count;

 

[laPistola]

Apprantly there is a bug in rand()

 

http://bugs.mysql.com/bug.php?id=17502

[fenway]

Apprantly there is a bug in rand()

 

http://bugs.mysql.com/bug.php?id=17502

What are you trying to do again?

[kevisazombie]

Here is a simplified query of something I am trying to do on a larger join query. It is still breaking on this small scale. I am trying to generate a random number for each row pulled back in the range of 1-60. I then want to order the returned rows by this random number.

 

 SELECT downloads . * , 
(FLOOR( 1 + ( RAND( ) *60 ) )) AS randomtimer
FROM downloads
ORDER BY randomtimer
LIMIT 25 

 

I have 2 databases I have tried this query on. A live one and a dev one. I have side by side compared the two and they are both structurally the same. It works correctly on the dev one. returning the rows ordered by the randomtimer.

 

The live table returns all 1's in the randomtimer column. If I order by randomtimer ASC they become all 60s. If I remove randomtimer from the Order By Clause it returns correct individual values. So something is tweaking the values on the ORDER BY statment.

 

Anyone have any ideas on this? Might I be overlooking something? WTF? WTF?

[kevisazombie]

Okay I canned the whole mysql idea and just made an array of random integers the same length as the results from the query and matched them up

[roopurt18]

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. However, you can retrieve rows in random order like this:

 

You could also try:

select a.*
  from (
    -- 25 random records
    select *, (floor( 1 + rand() * 60))) as randomtimer
    from downloads
    order by rand()
    limit 25
  ) as a
order by a.randomtimer

 

Note sure if that could be written any better.