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

Re: Random tuple?

From: Philip Hallstrom <philip(at)adhesivemedia(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Brendan LeFebvre <brendanl(at)iname(dot)com>,"Pgsql-Novice(at)Postgresql(dot) Org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Random tuple?
Date: 2002-10-18 23:50:22
Message-ID: 20021018164847.N52452-100000@cypress.adhesivemedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
> Brendan,
>
> > Is there available a routine, intrinsic or otherwise, that allows for simple
> > extraction of one(1) random tuple from a given table or query-result?
> >
> > (The specific application I had in mind was drawing a random product from a
> > webstore's inventory to feature on a front or section web page).
>
> SELECT tablea.*, random() as random_key
> FROM tablea
> ORDER BY random_key
> LIMIT 1;
>
> And you can modify the limit to select as many random rows as you want.

Question... does the above query have to "look" at every row in tablea,
assign each a random number, sort it, and then return just one result?

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?

Just thinking in terms of the amount of pounding the DB would take...

?

-philip


In response to

Responses

pgsql-novice by date

Next:From: Hubert depesz LubaczewskiDate: 2002-10-19 12:07:23
Subject: Re: Random tuple?
Previous:From: Jon JensenDate: 2002-10-18 23:13:11
Subject: Re: Random tuple?

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