> The rand() function returns a DOUBLE PRECISION number that is more than

zero and less than none. If this statement does not actually cause an
exception to be thrown (as I think it should), then the best it will do is
parse the ORDER BY clause *at the time the SP is compiled* and store the
result statically in the definition, as:

>

This is actually a method often used in MySQL (and therefore available on
forums if you search for it) to return a "random" row.

> ORDER BY 0

> -- which is meaningless. The DEGREE of a field is counted from left to

right in the SELECT clause, starting at 1 for the first field. And an ORDER
BY specification cannot be varied by a parameter or variable within the SP,
either...

>
> I expect you will find some creative way to fetch a random record from the

table, but this isn't it....even though it would be really great to fire off
a box of 12 Christmas crackers that have 12 *different* fortunes inside! ;-)

>

By far the easiest way, I would think, is to keep an array inside memory
of the primary key values, even it's a million rows, this will be relatively
small. Next, you randomly select a value using the favourite programming
language, and select that row from the table.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com