[oclug]MySQL question

[Joe Burpee]

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:

"6.4.1 SELECT Syntax

SELECT	...
	...
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
	..."

> I noticed that you can use an unsigned_integer after ORDER BY. What 
> significance is this unsigned integer? I have only ever used column names 
> after an ORDER BY clause.
> 
> 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.

> ) here:
> 
> http://www.postgresql.org/idocs/index.php?queries-order.html
> 
> it appears that putting a number after the ORDER BY signifies the column to 
> use. So if you use 1, it will order based on the first column.
> 
> Is this what the original OP wanted, to pick based on a random column? I would 
> think this would result in a similar few tuples being selected each time 
> given a slowly changing dataset.
> 
> 
> Unfortunately I am at a loss as to an efficient way to select a single tuple 
> at the moment. Maybe by morning :)
 
Have a good sleep.  ;-)
 
Anyway, as you may be aware, MySQL doesn't support subqueries, so you
could rule out some PostgreSQL-type solutions.

> > If you look at the MySQL optimizations at
> > http://www.mysql.com/doc/en/LIMIT_optimisation.html
> > you'll notice that
> >   "If you use LIMIT # with ORDER BY, MySQL will end the sorting as
> >   soon as it has found the first # lines instead of sorting the
> >   whole table."
> 
> I should hope it does :)
 
Regardless, that's what it says it does.

Joe

-- 
Joseph E. Burpee
GPG Fingerprint: 9188 AB4F A7B6 4AE5 9FB7  EF30 BE9B B1A2 9F6C BB8B