Re: Problem with ORDER BY and random() ?

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