Обсуждение: randomized order in select?

Hi,

I have got a simple table like this:
       create table tips (           id integer primary key unique,           tiptext text       );

and, I've got a website where I'm willing to show these tips in a random
order. Each visitor will get a randomly selected tip. So for now, I have
to do 2 queries:
select id from tips

collect an ID list, choose one randomly and retrieve it. I wish I could
do something like:
select tiptext from tips order by random limit 1

in PostgreSQL.
-- __________
|          |
|          |  Enver ALTIN (a.k.a. skyblue)
|          |  Software developer, IT consultant
|    FRONT |
|==========|  FrontSITE Bilgi Teknolojisi A.Ş.
|_____SITE_|  http://www.frontsite.com.tr/

On Wed, 10 Mar 2004, Enver ALTIN wrote:

> Hi,
> 
> I have got a simple table like this:
> 
>         create table tips (
>             id integer primary key unique,
>             tiptext text
>         );
> 
> and, I've got a website where I'm willing to show these tips in a random
> order. Each visitor will get a randomly selected tip. So for now, I have
> to do 2 queries:
> 
>     select id from tips
> 
> collect an ID list, choose one randomly and retrieve it. I wish I could
> do something like:
> 
>     select tiptext from tips order by random limit 1

You mean like:

select * from table order by random();

That Works as far back as 7.2, maybe before.

On Wed, Mar 10, 2004 at 18:48:17 +0200, Enver ALTIN <enver.altin@frontsite.com.tr> wrote:
> 
> collect an ID list, choose one randomly and retrieve it. I wish I could
> do something like:
> 
>     select tiptext from tips order by random limit 1
> 
> in PostgreSQL.

You can but it won't be very efficient (for large tables) as it will generate
a random ordering for the whole table, probably do a sort and then return the
first record.  The only thing different you need to do is add () after random:
select tiptext from tips order by random() limit 1

If the number of tips isn't very large doing the above is probably best.

On Wed, 2004-03-10 at 13:02 -0600, Bruno Wolff III wrote:
> You can but it won't be very efficient (for large tables) as it will generate
> a random ordering for the whole table, probably do a sort and then return the
> first record.  The only thing different you need to do is add () after random:
> select tiptext from tips order by random() limit 1

Thanks. The whole table consists of about 100 rows and I don't think it
will grow by, even twice. So this fits my needs just well.
-- __________
|          |
|          |  Enver ALTIN (a.k.a. skyblue)
|          |  Software developer, IT consultant
|    FRONT |
|==========|  FrontSITE Bilgi Teknolojisi A.Ş.
|_____SITE_|  http://www.frontsite.com.tr/

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...

Bruno Wolff III <bruno@wolff.to> writes:
>   Enver ALTIN <enver.altin@frontsite.com.tr> wrote:
>> I wish I could do something like:
>> select tiptext from tips order by random limit 1
>> in PostgreSQL.

> You can but it won't be very efficient (for large tables) as it will generate
> a random ordering for the whole table, probably do a sort and then return the
> first record.  The only thing different you need to do is add () after random:
> select tiptext from tips order by random() limit 1

If you do need to do this for a large table, I seem to recall that
we've previously worked out reasonable ways to select a random entry
efficiently using an index.  Dig around in the mail list archives for
details.
        regards, tom lane

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;