Re: randomized order in select? - Mailing list pgsql-sql

From scott.marlowe
Subject Re: randomized order in select?
Date
Msg-id Pine.LNX.4.33.0403110924161.11329-100000@css120.ihs.com
Whole thread Raw
In response to Re: randomized order in select?  ("Iain" <iain@mst.co.jp>)
List pgsql-sql
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;




pgsql-sql by date:

Previous
From: "Rute Solipa"
Date:
Subject: Re: About pg_dump
Next
From: Tom Lane
Date:
Subject: Re: About pg_dump