So here's my mission: return a random row from a MySQL table with no primary key. A quick search returned this page and this solution:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1;
Unfortunately, the solution is not optimized, as this site makes clear. They propose the following fix, which is in PHP:
$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );
Good enough as I'll be publishing this on a page, but in the meantime I need to try it in raw MySQL. So I punched in:
SELECT *
FROM `table`
LIMIT (SELECT FLOOR(RAND() * COUNT(*)) FROM `table`), 1;
... and got the following error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT FLOOR(RAND() * COUNT(*)) FROM `table`), 1' at line 3
But I don't understand, because the individual components work perfectly. If I enter:
SELECT FLOOR(RAND() * COUNT(*))
FROM `table`
... I get a random number from 0 up to the number of rows minus 1. Likewise, if I enter:
SELECT *
FROM `table`
LIMIT 2, 1
... I get the third row in the table (or the fourth row if I substitute 3 for 2, etc.)
What am I doing wrong?? Thanks in advance!