selecting random row

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/

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.

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