Postgres Pro
Downloads
Home   >   mailing lists

Thread: selecting random row

selecting random row

From
Heiko Klein
Date:
Hi,

out of a table i.e. people:
id last first sex age address

I would like to select a random id. Id's are a sequence, but some
peoble have been deleted, so there are several id-holes in the
table.

Furthermore, I would like to specify the random person, like

select 'random person id' from table where age > 60 and sex = 'm';



Right now (in perl), I select the complete list of id's into a @list, do
a $id = $list[rand(@list)] and have the right row. But this seems to be
very time-consuming, and I would like to have it done completely in the
Pg-database.

Regards,

Heiko

Re: selecting random row

From
Martijn van Oosterhout
Date:
On Tue, Apr 02, 2002 at 06:57:21AM +0000, Heiko Klein wrote:
> Hi,
>
> out of a table i.e. people:
> id last first sex age address
>
> I would like to select a random id. Id's are a sequence, but some
> peoble have been deleted, so there are several id-holes in the
> table.
>
> Furthermore, I would like to specify the random person, like
>
> select 'random person id' from table where age > 60 and sex = 'm';

select * from table where age > 60 and sex = 'm' order by random() limit 1;

IIRC

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>   http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing.  Speaking out against censorship and ignorance is the imperative
> of all intelligent people.

Re: selecting random row

From
Daniel Lundin
Date:
On tis, apr 02, 2002 at 06:57:21 +0000, Heiko Klein wrote:
> Hi,
>
> out of a table i.e. people:
> id last first sex age address
>
select id, random()
from people
order by 2
limit 1
;

The problem is that the entire table has to be sorted for each select. If it's
not very big, is suppose it's OK and better than your solution below.

> I would like to select a random id. Id's are a sequence, but some
> peoble have been deleted, so there are several id-holes in the
> table.
>
> Furthermore, I would like to specify the random person, like
>
> select 'random person id' from table where age > 60 and sex = 'm';
>
>
>
> Right now (in perl), I select the complete list of id's into a @list, do
> a $id = $list[rand(@list)] and have the right row. But this seems to be
> very time-consuming, and I would like to have it done completely in the
> Pg-database.
>
> Regards,
>
> Heiko
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: selecting random row

From
Holger Klawitter
Date:
Hi,

> Right now (in perl), I select the complete list of id's into a @list, do
> a $id = $list[rand(@list)] and have the right row. But this seems to be
> very time-consuming, and I would like to have it done completely in the
> Pg-database.

You can order by random and get the first row:

select
    id
from
    table
where
    agr>=60 and sex = 'm'
order by
    random()
limit
    1
;

This will also compute the whole result set - well, you have to in order
to get the value set to select from - but at least you don't have to
pull all data into your application tier.

With kind regards / Mit freundlichem Gruß
    Holger Klawitter
--
Holger Klawitter
holger@klawitter.de                             http://www.klawitter.de