Re: Problem with ORDER BY and random() ?

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