1

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?

example with three returned words, inclding table definition

Muleskinner
  • 14,150
  • 19
  • 58
  • 79

2 Answers2

2

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:

SELECT id, wword, FLOOR(1+RAND()*(10-1)) AS r FROM word_list;

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 to id 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:

SELECT word_list.*
FROM (SELECT FLOOR(1+RAND()*(10-1)) AS id) AS r
CROSS JOIN word_list USING (id);

Supposing your table is guaranteed to have one row for each id value between 1 and 10, this should return exactly one row.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I dont understand, doesnt `SELECT FLOOR(1 + RAND()*(10-1))` return ONE random number between 1 and 9? – Muleskinner Mar 18 '22 at 16:38
  • 2
    It returns one random number _each time the expression is evaluated_, and in your query it's evaluated once for each row. It's like you are rolling a die for each row. Each time you get a new random value, and that value might match the `id` of the row. – Bill Karwin Mar 18 '22 at 16:39
1

Another solution may be to order by RAND() than limit to 1. This method works if your table isn't too big.

SELECT * FROM word_list ORDER BY RAND() LIMIT 1;

Norbert Ds
  • 21
  • 3
  • Thanks / thats simpler than my query and as I have only 173528 rows it seems to perform aceptable for my purpose – Muleskinner Mar 18 '22 at 16:48