Re: 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
>
> Returns everything more or less as expected, except for the fact that the
> results aren't sorted by "id" ...

Of course not, they're already sorted randomly.  i.e. random() assigns a
value between 0 and 1 like so:

select random(),aid from accounts limit 10;
       random       | aid
--------------------+-----
  0.416937615450908 |   1
  0.398205195273368 |   2
   0.40122325271425 |   3
   0.68575628226891 |   4
 0.0215648445401177 |   5
 0.0346587472756667 |   6
  0.906103603498127 |   7
  0.347187338558579 |   8
  0.833244230986221 |   9
  0.786484897968585 |  10

So, if we make a subselect, we get:

select * from (select random() as r,aid from accounts limit 10) as a
order by a.r;
         r          | aid
--------------------+-----
 0.0806112047660217 |   8
 0.0979125742325152 |   4
  0.206458460170058 |   9
  0.492886080170463 |   5
  0.535966586571171 |   6
  0.553715904501135 |   2
  0.631926567122306 |   7
  0.761918006353973 |  10
  0.902183785523374 |   3
  0.978199429334234 |   1

We can see what number we were ordering by.  Since the chances of having
two random numbers be the same float is pretty close to zero, the order by
random(),id will never get to the id, because random() has no repeating
values.