[EMAIL PROTECTED] 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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
