Problem with ORDER BY and random() ?

[Jean-Francois.Doyon]

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: [phone]
Fax: [phone]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Christopher Browne]

Centuries ago, Nostradamus foresaw when Je*****************@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/>

[Dennis Gearon]

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

Je*****************@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" ...

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: [phone]
Fax: [phone]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[scott.marlowe]

On Tue, 23 Sep 2003 Je*****************@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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Je*****************@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

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

[Stephan Szabo]

On Tue, 23 Sep 2003 Je*****************@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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Mike Mascari]

Je*****************@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
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Nigel J. Andrews]

On Tue, 23 Sep 2003 Je*****************@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