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

score 121 · Accepted Answer · answered Jun 12 '09 at 00:08

121

This is working for me. Your mysql version maybe?

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

answered Jun 12 '09 at 00:08

Ryan Oberoi

13,817
2
24
23

1

But this query repeats values – PHPFan Mar 10 '20 at 09:59

score 3 · Answer 2 · answered Mar 22 '21 at 10:29

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;

score 3 · Answer 3 · edited Jul 28 '14 at 18:21

3

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

edited Jul 28 '14 at 18:21

Michel Ayres

5,891
10
63
97

answered Jun 12 '09 at 00:10

Chris Morley

2,426
2
19
20

1

you can use floor instead of casting to int, casting uses round. – Felipe Buccioni Jun 13 '13 at 14:27

score 2 · Answer 4 · answered Jun 12 '09 at 00:07

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

answered Jun 12 '09 at 00:07

Jaime

6,736
1
26
42

score 0 · Answer 5 · answered Feb 04 '15 at 15:25

0

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

answered Feb 04 '15 at 15:25

e_r_k_a_n e_r_t_u_r_a_l

21
3

How to get mysql random integer range?

5 Answers5

Linked

Related