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

Re: randomized order in select?

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Iain <iain(at)mst(dot)co(dot)jp>
Cc: Enver ALTIN <enver(dot)altin(at)frontsite(dot)com(dot)tr>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: randomized order in select?
Date: 2004-03-11 16:28:43
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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;

In response to

pgsql-sql by date

Next:From: scott.marloweDate: 2004-03-11 17:14:17
Subject: Re: designer tool connect to PostgreSQL
Previous:From: Jonathan M. GardnerDate: 2004-03-11 16:22:01
Subject: Re: Break a report in Run Time

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