Problem with ORDER BY and random() ?

Centuries ago, Nostradamus foresaw when Jean-Francois.Doyon@CCRS.NRCan.gc.ca would write:

I'm trying to retrieve a limited number of random rows, and order them by a
column, and am not having any luck with that last part:

SELECT * FROM tablename ORDER BY random(), id LIMIT 10

Returns everything more or less as expected, except for the fact that the
results aren't sorted by "id" ...

I'm not sure why you are surprised at this. You asked to order them
primarily by random(), and then by id, if the random values were
equal, and that is clearly the order that you are getting.

If you want to order the 10 records by id, you might accomplish that
by the following:

select * from (select * from tablename order by random() limit 10)
order by id;
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/finances.html
Rules of the Evil Overlord #62. "I will design fortress hallways with
no alcoves or protruding structural supports which intruders could use
for cover in a firefight." <http://www.eviloverlord.com/&gt;

On Tue, 23 Sep 2003 Jean-Francois.Doyon@ccrs.nrcan.gc.ca wrote:

Hello,

I'm trying to retrieve a limited number of random rows, and order them by a
column, and am not having any luck with that last part:

SELECT * FROM tablename ORDER BY random(), id LIMIT 10

Returns everything more or less as expected, except for the fact that the
results aren't sorted by "id" ...

Of course not, they're already sorted randomly. i.e. random() assigns a
value between 0 and 1 like so:

select random(),aid from accounts limit 10;
random | aid
--------------------+-----
0.416937615450908 | 1
0.398205195273368 | 2
0.40122325271425 | 3
0.68575628226891 | 4
0.0215648445401177 | 5
0.0346587472756667 | 6
0.906103603498127 | 7
0.347187338558579 | 8
0.833244230986221 | 9
0.786484897968585 | 10

So, if we make a subselect, we get:

select * from (select random() as r,aid from accounts limit 10) as a
order by a.r;
r | aid
--------------------+-----
0.0806112047660217 | 8
0.0979125742325152 | 4
0.206458460170058 | 9
0.492886080170463 | 5
0.535966586571171 | 6
0.553715904501135 | 2
0.631926567122306 | 7
0.761918006353973 | 10
0.902183785523374 | 3
0.978199429334234 | 1

We can see what number we were ordering by. Since the chances of having
two random numbers be the same float is pretty close to zero, the order by
random(),id will never get to the id, because random() has no repeating
values.

Jean-Francois.Doyon@CCRS.NRCan.gc.ca writes:

I'm trying to retrieve a limited number of random rows, and order them by a
column, and am not having any luck with that last part:
SELECT * FROM tablename ORDER BY random(), id LIMIT 10
Returns everything more or less as expected, except for the fact that the
results aren't sorted by "id" ...

Well, no. You specified random() as the major sort key. Only rows that
happened to have equal random() values would be sorted by id.

This would work:

SELECT * FROM
(SELECT * FROM tablename ORDER BY random() LIMIT 10) as ss
ORDER BY id;

regards, tom lane

I just searched all the 7.2 manuals, the latest I have, and there is not
one explanation of using random to order a query........

Jean-Francois.Doyon@ccrs.nrcan.gc.ca wrote:

Show quoted text

Hello,

I'm trying to retrieve a limited number of random rows, and order them by a
column, and am not having any luck with that last part:

SELECT * FROM tablename ORDER BY random(), id LIMIT 10

Returns everything more or less as expected, except for the fact that the
results aren't sorted by "id" ...

I also tried:

SELECT random() as sorter, * FROM tablename ORDER BY sorter, id LIMIT 10

But that didn't change anything either.

I tried sorting on a column other than "id", but that didn't work any better
:(

I also tried this on 7.2.1 and 7.3.1 (Both on RH 7.3), thinking this
might've been a bug.

A quick read of the docs suggests sorting on multiple columns is perfectly
legal, as it is used as an example.

atlas=# select id from quiz_questions_english order by random(), id limit
10;
id
-----
445
756
393
809
335
682
776
754
379
739
(10 rows)

atlas=# select random() as sorter, id from quiz_questions_english order by
sorter, id limit 10;
sorter | id
----------------------+-----
0.000757388770932978 | 455
0.00806515943634564 | 440
0.00836807396652553 | 386
0.00977775268711976 | 323
0.0104504898239162 | 370
0.0166072882789221 | 778
0.0202831137088514 | 416
0.0306016304672703 | 762
0.0340994806187691 | 772
0.0384632679812905 | 371
(10 rows)

Anybody know what's going on here ? I've tried this from Zope/psycopg,
pgAdminII, and psql ... removing the limit doesn't do any good, and neither
does using ASC or DESC !

Any help would be greatly appreciated !!

Thanks in advance,

Jean-Fran?ois Doyon
Internet Service Development and Systems Support
GeoAccess Division
Canadian Center for Remote Sensing
Natural Resources Canada
http://atlas.gc.ca
Phone: (613) 992-4902
Fax: (613) 947-2410

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

On Tue, 23 Sep 2003 Jean-Francois.Doyon@CCRS.NRCan.gc.ca wrote:

Hello,

I'm trying to retrieve a limited number of random rows, and order them by a
column, and am not having any luck with that last part:

SELECT * FROM tablename ORDER BY random(), id LIMIT 10

Returns everything more or less as expected, except for the fact that the
results aren't sorted by "id" ...

The above basically says sort by random() and then for equal values of
that sort those by id. That's not going to sort by id really except in
cases that random() gave the same value. You probably wanted something
with a subselect that did the limiting with the order by on the outside,
like:
SELECT * from (SELECT * FROM tablename ORDER BY random() LIMIT 10) as foo
ORDER BY id;

However, this is a fairly expensive way to generate random rows for a big
table. The archives should have some better mechanisms in them.

Jean-Francois.Doyon@CCRS.NRCan.gc.ca wrote:

Hello,

I'm trying to retrieve a limited number of random rows, and order them by a
column, and am not having any luck with that last part:

SELECT * FROM tablename ORDER BY random(), id LIMIT 10

How about:

SELECT * FROM
(SELECT * FROM tablename ORDER BY random() LIMIT 10) AS data
ORDER BY id;

HTH,

Mike Mascari
mascarm@mascari.com

Ahhhh, ok :)

Sorry, I just misunderstood how the sorting was being applied, I thought it
would sort on one, and then the next, not on one, and then theother where
the first is equal !

I thought I'd tried a nested SQL, but had gotten some errors ...

Anyways thanks to your examples I got it working!

Thanks to you all for the quick replies!

J.F.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, September 23, 2003 6:31 PM
To: Jean-Francois.Doyon@CCRS.NRCan.gc.ca
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem with ORDER BY and random() ?

Jean-Francois.Doyon@CCRS.NRCan.gc.ca writes:

I'm trying to retrieve a limited number of random rows, and order them by

a

column, and am not having any luck with that last part:
SELECT * FROM tablename ORDER BY random(), id LIMIT 10
Returns everything more or less as expected, except for the fact that the
results aren't sorted by "id" ...

Well, no. You specified random() as the major sort key. Only rows that
happened to have equal random() values would be sorted by id.

This would work:

SELECT * FROM
(SELECT * FROM tablename ORDER BY random() LIMIT 10) as ss
ORDER BY id;

regards, tom lane

On Tue, 23 Sep 2003 Jean-Francois.Doyon@CCRS.NRCan.gc.ca wrote:

Hello,

I'm trying to retrieve a limited number of random rows, and order them by a
column, and am not having any luck with that last part:

SELECT * FROM tablename ORDER BY random(), id LIMIT 10

So it's sorting by random() then id.

Returns everything more or less as expected, except for the fact that the
results aren't sorted by "id" ...

Well it's sorting by random() then id.

SELECT random() as sorter, * FROM tablename ORDER BY sorter, id LIMIT 10

Sorting by sorter then id.

But that didn't change anything either.

Well it wouldn't because it's sorting by random() then id. :)

I tried sorting on a column other than "id", but that didn't work any better
:(

Well no. It's sorting by random() then some column other than id. :))

I also tried this on 7.2.1 and 7.3.1 (Both on RH 7.3), thinking this
might've been a bug.

Looks alright to me.

A quick read of the docs suggests sorting on multiple columns is perfectly
legal, as it is used as an example.

Yep, but then the example probably isn't trying to sort by random() then
id. :)))

atlas=# select id from quiz_questions_english order by random(), id limit
10;
id
-----
445
756
393
809
335
682
776
754
379
739
(10 rows)

So it's sorting by random() the id? *big big grin*

atlas=# select random() as sorter, id from quiz_questions_english order by
sorter, id limit 10;
sorter | id
----------------------+-----
0.000757388770932978 | 455
0.00806515943634564 | 440
0.00836807396652553 | 386
0.00977775268711976 | 323
0.0104504898239162 | 370
0.0166072882789221 | 778
0.0202831137088514 | 416
0.0306016304672703 | 762
0.0340994806187691 | 772
0.0384632679812905 | 371
(10 rows)

Ah, yes, I see now, it's sorting by random() then id. *falls on floor giggling
like a little school girl*

Anybody know what's going on here ? I've tried this from Zope/psycopg,
pgAdminII, and psql ... removing the limit doesn't do any good, and neither
does using ASC or DESC !

Sheesh, now all I've got to do is remember the suggested ways of doing this.

select *
from (select random(), id from quiz_questions order by 1 limit 10) ss
order by id

should at least get you closer I think.

--
Nigel J. Andrews

so.. what exactly are you trying to say here? ;)
I think this is the funniest post I've ever seen on this list!!!

Travis

-----Original Message-----
From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
Sent: Tuesday, September 23, 2003 5:47 PM
To: Jean-Francois.Doyon@CCRS.NRCan.gc.ca
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Problem with ORDER BY and random() ?

On Tue, 23 Sep 2003 Jean-Francois.Doyon@CCRS.NRCan.gc.ca wrote:

Hello,

I'm trying to retrieve a limited number of random rows, and order them

by a

column, and am not having any luck with that last part:

SELECT * FROM tablename ORDER BY random(), id LIMIT 10

So it's sorting by random() then id.

Returns everything more or less as expected, except for the fact that

the

results aren't sorted by "id" ...

Well it's sorting by random() then id.

SELECT random() as sorter, * FROM tablename ORDER BY sorter, id LIMIT

10

Sorting by sorter then id.

But that didn't change anything either.

Well it wouldn't because it's sorting by random() then id. :)

I tried sorting on a column other than "id", but that didn't work any

better

:(

Well no. It's sorting by random() then some column other than id. :))

I also tried this on 7.2.1 and 7.3.1 (Both on RH 7.3), thinking this
might've been a bug.

Looks alright to me.

A quick read of the docs suggests sorting on multiple columns is

perfectly

legal, as it is used as an example.

Yep, but then the example probably isn't trying to sort by random() then
id. :)))

atlas=# select id from quiz_questions_english order by random(), id

limit

10;
id
-----
445
756
393
809
335
682
776
754
379
739
(10 rows)

So it's sorting by random() the id? *big big grin*

atlas=# select random() as sorter, id from quiz_questions_english

order by

sorter, id limit 10;
sorter | id
----------------------+-----
0.000757388770932978 | 455
0.00806515943634564 | 440
0.00836807396652553 | 386
0.00977775268711976 | 323
0.0104504898239162 | 370
0.0166072882789221 | 778
0.0202831137088514 | 416
0.0306016304672703 | 762
0.0340994806187691 | 772
0.0384632679812905 | 371
(10 rows)

Ah, yes, I see now, it's sorting by random() then id. *falls on floor
giggling
like a little school girl*

Anybody know what's going on here ? I've tried this from Zope/psycopg,
pgAdminII, and psql ... removing the limit doesn't do any good, and

neither

does using ASC or DESC !

Sheesh, now all I've got to do is remember the suggested ways of doing
this.

select *
from (select random(), id from quiz_questions order by 1 limit 10) ss
order by id

should at least get you closer I think.

--
Nigel J. Andrews

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html