Thread: Random tuple?
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).
On Fri, 18 Oct 2002, Brendan LeFebvre wrote: > 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? How about: SELECT * FROM your_table ORDER BY random() LIMIT 1 Jon
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. -- -Josh Berkus Aglio Database Solutions San Francisco
> 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
Phil, > 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? Yeah. It's resource-intensive if you have, say, 8 million rows. > 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... Well, if you have a large table, the logical approach would be a PL/pgSQL function that selects a random row based on the number of rows in the table * random, and then picks a row based on LIMIT and OFFSET. This would be somewhat less resource-intensive for a large table, but has the disadvantage that it would have to be re-run for each random row you wanted to retrieve. -- -Josh Berkus Aglio Database Solutions San Francisco
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