I am trying to get a random word from a long list of words in a MySql table: word_list [id*|wword]
.
My query looks at the moment like this:
SELECT * FROM word_list w where id = (SELECT FLOOR(1 + RAND()*(10-1)))
I thought the above query would return one and only one random row with Id between 1 and 9.
To my surprise it sometimes returns 0, 1, 2 or 3 rows. How is this possible?
It's calculating a new random number for each row. When the result of the random expression matches the
id
, then that row is included in the result.This should demonstrate how your query is working:
This returns a different random number on each row (which is also what you're doing with your random expression in the WHERE clause). Sometimes the random number returned as
r
is equal toid
on the respective row. Not often, but occasionally. When these two numbers happen to be equal, that's when a row would be returned in your query.What you need instead is to calculate a random number once. For example:
Supposing your table is guaranteed to have one row for each
id
value between 1 and 10, this should return exactly one row.