[oclug]MySQL question

[Ian Wormsbecker]

On January 19, 2003 11:54 pm, Joe Burpee wrote:
> On Sun, Jan 19, 2003 at 10:04:26PM -0500, Ian Wormsbecker wrote:
> > On January 19, 2003 09:41 pm, Joe Burpee wrote:
> > > SELECT * FROM sponsor ORDER BY rand() LIMIT 1;
> >
> > How does this ORDER BY rand() work? What does the ORDER BY do with the
> > rand() arg do? I looked at the mysql docs and the only part I could find
> > talking about ORDER BY was here:
> >
> > http://www.mysql.com/doc/en/SELECT.html
>
> Why so many questions?  :-(  You could just try the query, or even RTFM:
>

I had so many questions that were not answered by the mysql docs, so I decided 
to look at the docs for a database that I was more familiar with. I could not 
find the location in the mysql docs where it explains the purpose of using an 
unsigned_integer after an ORDER BY. 

I was also concerned that 'just try the query' may provide a result that looks 
good to start, but doesn't actually work the way one would expect. Even the 
OP had this issue, most times the query worked as expected, but sometimes it 
doesn't because it returns two tuples.

So in an effort to determine how this ORDER BY rand() works, I installed mysql 
and ran some queries. I am still not convinced I understand how mysql uses 
the unsigned_integer in the ORDER BY, but it is still early morning :)

I broke the query down into something I find more understandable. I am not 
convinced that it is the same as the suggested "SELECT * FROM sponsor ORDER 
BY rand() LIMIT 1;"

SELECT rand() as r, mytable.* FROM mytable ORDER BY r LIMIT  1;

mysql> SELECT rand() as r, mytable.* FROM mytable ORDER BY r;        
+-------------------+----------+----------+----------+
| r                 | column_1 | column_2 | column_3 |
+-------------------+----------+----------+----------+
| 0.224717011884523 | Ian      | Ottawa   | East     |
|  0.41832160895534 | a        | a        | a        |
| 0.490412572855514 | Joe      | Canada   | West     |
|  0.53439769105464 | Z        | Z        | Z        |
| 0.757872497437403 | z        | z        | z        |
| 0.777911859357648 | Other    | Ontario  | Central  |
+-------------------+----------+----------+----------+
6 rows in set (0.00 sec)

mysql> SELECT rand() as r, mytable.* FROM mytable ORDER BY r LIMIT  1;
+--------------------+----------+----------+----------+
| r                  | column_1 | column_2 | column_3 |
+--------------------+----------+----------+----------+
| 0.0184213677592775 | z        | z        | z        |
+--------------------+----------+----------+----------+
1 row in set (0.01 sec)



> >
> > Looking at the postgresql docs ( I typically use postgresql so...
>
> So... what.  Given the user's question, I don't see the point of referring
> to PostgreSQL docs.


Ian