Re: Problem with ORDER BY and random() ? - Mailing list pgsql-general

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