Re: Problem with ORDER BY and random() ?

On Tue, 23 Sep 2003 Jean-Francois(dot)Doyon(at)CCRS(dot)NRCan(dot)gc(dot)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" ...

The above basically says sort by random() and then for equal values of
that sort those by id. That's not going to sort by id really except in
cases that random() gave the same value. You probably wanted something
with a subselect that did the limiting with the order by on the outside,
like:
SELECT * from (SELECT * FROM tablename ORDER BY random() LIMIT 10) as foo
ORDER BY id;

However, this is a fairly expensive way to generate random rows for a big
table. The archives should have some better mechanisms in them.