Re: randomized order in select?

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;