[EMAIL PROTECTED] wrote:
>
> Is there any way I can fetch a totally random row with a single query? I
> know I can do a count(*); i = rand<result>; <query> limit <i>,1 but that
> seems inelegant. not to mention wasteful, making the server scan through
> all those extra lines to get the one i want.
This is far from elegant and requires 2 elements, but seems to work.
SELECT *, ceiling(rand()*3) AS r FROM tablename
HAVING num=r OR num=1
ORDER BY num DESC
LIMIT 1;
You must know the number of rows in the table (that's the 3 in the
example) and the 'num' field must be unique.
The reason for 'OR num=1' is because r can be 0.
Hope this helps,
jim...
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
