From: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
---|---|
To: | Jean-Francois(dot)Doyon(at)CCRS(dot)NRCan(dot)gc(dot)ca |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problem with ORDER BY and random() ? |
Date: | 2003-09-23 22:46:35 |
Message-ID: | Pine.LNX.4.21.0309232338040.25713-100000@ponder.fairway2k.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 23 Sep 2003 Jean-Francois(dot)Doyon(at)CCRS(dot)NRCan(dot)gc(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Williams, Travis L, NEO | 2003-09-23 23:24:18 | Re: Problem with ORDER BY and random() ? |
Previous Message | Jean-Francois.Doyon | 2003-09-23 22:41:48 | Re: Problem with ORDER BY and random() ? |