Skip site navigation (1) Skip section navigation (2)

Re: Random tuple?

From: Hubert depesz Lubaczewski <depesz(at)depesz(dot)pl>
To: Philip Hallstrom <philip(at)adhesivemedia(dot)com>,pgsql-novice(at)postgresql(dot)org
Subject: Re: Random tuple?
Date: 2002-10-19 12:07:23
Message-ID: 20021019120723.GA4884@depesz.pl (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: working4alivingDate: 2002-10-20 11:28:14
Subject: Re: Help with the big picture
Previous:From: Philip HallstromDate: 2002-10-18 23:50:22
Subject: Re: Random tuple?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group