Обсуждение: Problem with ORDER BY and random() ?
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
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: >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" ...
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
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
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/>