By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,773 Members | 1,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,773 IT Pros & Developers. It's quick & easy.

Random not so random

P: n/a
Hi everybody,

I'm doing the following query:

select * from messages order by random() limit 1;

in the table messages I have more than 200 messages and a lot of times, the
message retrieved is the same. Anybody knows how I could do a more "random"
random?

Thank you very much

--
Arnau

__________________________________________________ _______________
Consigue aquí las mejores y mas recientes ofertas de trabajo EE.UU.
http://latino.msn.com/empleos
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Use a SERIAL id on messages, then

Select * from messages
where id = int8( random() * currval({sequence_name}));

Arnau Rebassa wrote:
Hi everybody,

I'm doing the following query:

select * from messages order by random() limit 1;

in the table messages I have more than 200 messages and a lot of times,
the message retrieved is the same. Anybody knows how I could do a more
"random" random?

Thank you very much

--
Arnau

__________________________________________________ _______________
Consigue aquí las mejores y mas recientes ofertas de trabajo EE.UU.
http://latino.msn.com/empleos
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

P: n/a

"Arnau Rebassa" <ar******@hotmail.com> writes:
select * from messages order by random() limit 1;

in the table messages I have more than 200 messages and a lot of times, the
message retrieved is the same. Anybody knows how I could do a more "random"
random?


What OS is this? Postgres is just using your OS's random()/srandom() calls. On
some platforms these may be poorly implemented and not very random.

However of the various choices available I think random/srandom are a good
choice. I'm surprised you're finding it not very random.

Incidentally, are you reconnecting every time or is it that multiple calls in
a single session are returning the same record? It ought not make a difference
as Postgres is careful to seed the random number generator with something
reasonable though.

In a quick test of my own on linux with glibc 2.3.2.ds1 (no, I have no idea
what the ds1 means) It seems fairly random to me:

test=> create table test4 as (select (select case when b.b then a else a end from test order by random() limit 1) as b from b limit 1000);
SELECT
test=> select count(*),b from test4 group by b;
count | b
-------+---
210 | 5
195 | 4
183 | 3
203 | 2
209 | 1
(5 rows)

And the same thing holds if I test just the low order bits too:

test=> create table test4 as (select (select case when b.b then a else a end from test order by random() limit 1) as b from b limit 1000);
SELECT
test=> select count(*),b from test4 group by b;
count | b
-------+---
249 | 4
241 | 3
259 | 2
251 | 1
(4 rows)

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.