Search code examples
mysqlsqlrandomcountfloor

why am I getting multiple rows in MYSQL query?

I am trying to retrive a random row from a table (user), by using the Primary Key _id and the following query.

SELECT * 
FROM   user 
WHERE  _id IN (SELECT Floor(1 + ( Rand() * ( Count(_id) - 1 ) )) 
               FROM   user);

No rows have been deleted from the table but I (inconsistently) get multiple rows returned...

I find this amazing, as any result from the subquery should surely return one integer, which against a primary key.....should always only be 1 record!

I want to use one query, I will probably make it a prepared statement, and I am not using the LIMIT clause because I cannot use @variables to pick the random row.

My MySQL welcome statement tells me my version is as follows: Server version: 5.5.29-0ubuntu0.12.04.2 (Ubuntu)

Solution

  • How about using ORDER BY RAND() LIMIT 1; in your query instead of where _id IN (select FLOOR(1+(RAND()*(COUNT(_id)-1))) from user);

    You get multiple rows cause (select FLOOR(1+(RAND()*(COUNT(_id)-1))) from user) will return you a different value for ever record in 'user' table. That is because of the RAND bit.

    Run select FLOOR(1+(RAND()*(COUNT(_id)-1))) from user on its own and you will see.