[oclug]MySQL question

[B McKee]

Thanks guys for the good info - When I hit a head scratcher like that I start 
questioning everything.  If it's generating a new number for each each row, 
then I'm not likely to get the results I want.  Sorting and then picking one 
sounds like a lot of processing for what should be a simple task.  Since the 
query is to be made by a perl script (that's the next step) I think I'll have 
perl generate the random number before I pass the query to the database.   It 
will take a second query to determine the number of rows first, but come to 
think of it, I don't think I could have done that with a single query anyway.
Next step? Now I have to figure out why DBI doesn't want to install on my 
machine....
Brian
PS  Your tip re round(x) vs round(x,y) was interesting - the documentation I 
had stated that round with no number of digits argument rounded off the last 
decimal place.  You are right though - it instead rounds off all decimal 
places on my machine.  The link below suggested truncate as a more 
predicatable result.

On January 19, 2003 03:53 pm, you wrote:
> On Sun, Jan 19, 2003 at 03:08:14PM -0500, Rod Giffin wrote:
> > On Sun, 2003-01-19 at 14:49, Rod Giffin wrote:
> > > Try changing your SQL to
> > > SELECT  name, sponsor_id FROM sponsor WHERE sponsor_id
> > > =round(rand()*3,0) LIMIT 1;
> >
> > Limit is particularly useful if you have a query that can return many
> > records but during development or testing you don't need to return all
> > of the records.
>
> I think it's also useful for random sampling.  But for that purpose, the
> use of WHERE with LIMIT makes me nervous since it looks order-dependent,
> therefore potentially biased.
>
> The discussion of RAND() at
> http://www.mysql.com/doc/en/Mathematical_functions.html
> clarifies why WHERE can return multiple rows (you get a new random
> number for each row).  It also illustrates `ORDER BY ... LIMIT ...' for
> sampling.
>
> ORDER BY could be inefficient for a large table, but it could be speeded
> up by only applying it to an intermediate subtable.  (E.g. in this case
> first use WHERE; then use `ORDER BY RAND() LIMIT 1' on the result or
> subquery.)
>
> Joe

-- 
####[ Linux One Stanza Tip (LOST) ]###########################
Sub : Accessing ext2 through Windows                 LOST #186
Download a program called "explore2fs" from:
http://uranus.it.swin.edu.au/~jn/linux/explore2fs.htm
and install it in win9x partition ...  It gives explorer like 
interface. WARNING! Write to ext2 is still alpha ..  use with
caution ... ext2 partition read is 100% safe.                                 
                                                   
####<lvgandhi at md3.vsnl.net.in>################################