Re: Random tuple?

On Fri, Oct 18, 2002 at 04:50:22PM -0700, Philip Hallstrom wrote:
> If so, would it make more sense to setup a periodic process (via cron say)
> to do extract 10-20 products into a featured_product table and query that?

from my experience, the best way is something like this - create (or use
existing) id field, based on serial.
now. with two queries;
select id from table order by id asc limit 1;
select id from table order by id desc limit 1;
get min and max values, and then choose randomly value between them.

next, try to fetch a row with this id. if it doesn't exist, choose
another value, and try again until success.

this is of course very bad way when your deleted to existing rows ratio
is too high, but usually you can rely on this method as of one of the
fastest.

depesz

--
hubert depesz lubaczewski http://www.depesz.pl/
------------------------------------------------------------------------
Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam
coś do powiedzenia. (c) 1998 depesz