Re: Problem with ORDER BY and random() ?

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