I am trying to a get a single random row back from a table and to do this I am can using FLOOR(1 + rand() * 50) to get a valid row ID. 50 equals the amount of records in my table (I actually have a couple of thousand but as an example I'm keeping this small).
The SQL ends up being this
<!-- language: lang-sql -->
SELECT id FROM ids WHERE id = FLOOR(1 + rand() * 50)
But when I run this query either returns
The issue is that I always need 1 record back; I could put a LIMIT on there but sometimes I even don't get a record back plus I shouldn't need to as FLOOR(1 + rand() * 50) will always return a valid row ID.
I know there are other ways I can do this but now I just need to understand why this is happening. To demonstrate here is my example table
<!-- language: lang-sql -->
CREATE TABLE `ids` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I then run the following 50 times
<!-- language: lang-sql -->
INSERT INTO `ids` (`id`) VALUES (NULL);
So now my table looks like this (but with another 48 records underneath)
id |
---|
1 |
2 |
.
.
With the table of IDs set up I proceed to keep running the first query, remembering that FLOOR(1 + rand() * 50) always returns a valid ID within range and I get
id |
---|
25 |
30 |
43 |
or
id |
---|
or
id |
---|
41 |
Declaring the ID you are going to look up does actually get around this problem.
SET @randomID = FLOOR(1 + rand() * 50);
SELECT id FROM ids WHERE id = @randomID;
Though I am still not understanding why I get more than 1 record back in the original query.
The standard docs says -
RAND() in a WHERE clause is evaluated for every row (when selecting from one table)
If you don't want to declare a variable -
select id from `ids`
join (select FLOOR(1 + rand() * 50) as id) b using(id);