Selecting random entry in MySQL with WHERE clause problem

Question

Hi all,

I'm running the following code to get a random entry from a dictionary:

SELECT * FROM tbl_dict WHERE 1 ORDER BY RAND() LIMIT 1

This works great, but as soon as I expand there WHERE clause the query fails. What I need is something like...

SELECT * FROM tbl_dict WHERE 1 and lock='0' ORDER BY RAND() LIMIT 1

Can anyone point out to me where I'm going wrong? My mind has turned to peanut butter.

Thank you!

Answer 1

Never mind, this is wrong for what you're trying to do.

You do have a synthetic auto_incremented id column on your table, right?

if so, then why not:

  select * from tbl_dict where id 
   = (select floor( rand() * ( max(b.id) + 1) ) from tabl_dict b );

Answer 2

lock is a reserved word for MySQL. You have to put it in backticks (`) or double-quotes (in ansi mode) if you want to use it as an identifier.

You might also be interested in http://jan.kneschke.de/projects/mysql/order-by-rand.
ORDER BY Rand() might or might not be a bit too suboptimal in your case when it comes to speed.