X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A8AFFD1E9BD for ; Thu, 11 Mar 2004 16:34:03 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 20455-04 for ; Thu, 11 Mar 2004 12:33:51 -0400 (AST) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 74CA7D1EC9A for ; Thu, 11 Mar 2004 12:31:59 -0400 (AST) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.10/8.12.10) with ESMTP id i2BGUTSn029543; Thu, 11 Mar 2004 09:30:29 -0700 (MST) Date: Thu, 11 Mar 2004 09:28:43 -0700 (MST) From: "scott.marlowe" To: Iain Cc: Enver ALTIN , Subject: Re: randomized order in select? In-Reply-To: <009301c4072a$19b28fd0$7201a8c0@mst1x5r347kymb> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-IHS-MailScanner: Found to be clean X-IHS-MailScanner-SpamCheck: X-IHS-MailScanner-Envelope-Sender: scott.marlowe@ihs.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/131 X-Sequence-Number: 17230 On Thu, 11 Mar 2004, Iain wrote: > If you have a lot of tips, you could create a unique indexed tip number > column. Select the highest tip number using: > > select tip_number from tips order by tip_number desc limit 1; > > Then generate a random number and select using that tip_number. > > Of course, you would have to allow for the possibility of missing tip > numbers, by repeating the random number generation/read sequence until you > find something. Since the tip_number isn't the PK of the table, you can > regenerate the tip numbers to eliminate holes from deletions any time you > like. Just reset the sequence to 1 and update all rows with the > nextval(tipnumber_seq). > > Sounds like a lot of work to me though... Assuming there are ten rows, you can use this: select * from table limit 1 offset random()*10;