It was necessary to write a SQL query to select a random entry, without hesitation, opened the MySQL documentation, read about the random and it turned out this

SELECT * FROM `Task` WHERE id = (FLOOR(1 + RAND() * (SELECT Count(`id`) FROM `Task`)))

For the first time everything is ok (lucky), then 3 records, then 0, well, and so on ... Why?

  • one
    like 3 entries? Your id is not unique? Not primary key? - vp_arth
  • vp_arth, even if it is not Primary (it is Primary) The sample conditions are there - BigTows
  • limit 1 do not worry))) in general it would be nice to see it clearly, for example, you can put a link here on sqlfiddle.com - Alexey Shimansky
  • @ Alexey Shimansky, I set a limit (Okay 1 crutch), but it happens that 0 records fall out and this is not due to the randomness (Spawn 1 - n), I even know how to solve it. But it will turn out already 2 crutches, and this is not fun anymore) - BigTows
  • limit 1 , count replaced by max , a = and < and the floor removed. Otherwise, it is impossible to get rid of empty samples, and the lines with id>count(id) do not shine - vp_arth

1 answer 1

MySQL re-calculates the value of a random number for each entry. let's see what happens:

 SELECT id, (FLOOR(1 + RAND() * ((SELECT Count(id) FROM Test)))) rnd FROM Test

result:

 '1', '4' '2', '12' '3', '10' '4', '12' '5', '8' '6', '1' '7', '5' '8', '10' '9', '9' '10', '11' '11', '9' '12', '12'

And now we impose our filter id=rnd on this, at the output of the line:

 '9', '9' '12', '12'

Total 2 entries. It just so happened that at the time of selecting these id's from the rand() database, it gave the exact same value and the condition worked. If we are unlucky, rand() may not give a matching value for one line.

So it will be reliable:

 SELECT id FROM Test, (select (FLOOR(1 + RAND() * ((SELECT Count(id) FROM Test)))) rnd) X WHERE id=rnd

In this case, MySQL first calculates (once) rand() , and then it will look for the record with the necessary id.

  • Thank! I even just wondered why it didn’t work, now it’s clear. - BigTows