List:General Discussion« Previous MessageNext Message »
From:John Kebbel Date:April 30 2007 6:41pm
Subject:RE: Research Subjects drawn randomly from databases
View as plain text  
id is an integer ...

describe persons;
+------------+---------------------------+------+-----+---------+-------+
| Field      | Type                      | Null | Key | Default | Extra
|
+------------+---------------------------+------+-----+---------+-------+
| ID         | int(11)                   | YES  |     | NULL    |       

I got a 0 count ...

SELECT COUNT(*) FROM persons WHERE ROUND(id) != id;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.06 sec)


On Mon, 2007-04-30 at 10:45 -0400, Jerry Schwartz wrote:
> Is your ID field an integer? If not, you might be running into some rounding
> corner cases. I don't see why that would happen, off-hand, since integers
> can be stored exactly as binary floating point numbers, but who knows.
> 
> To satisfy your curiosity, you could
> 
> SELECT COUNT(*) FROM table WHERE ROUND(id) != id;
> 
> If you get a non-zero count, then you know that there is a possibility of
> CEIL(RND()) not hitting an ID.
> 
> Regards,
> 
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
> 
> 860.674.8796 / FAX: 860.674.8341
> 
> 
> > -----Original Message-----
> > From: John Kebbel [mailto:kebbelj@stripped]
> > Sent: Sunday, April 29, 2007 11:49 AM
> > To: MySQL
> > Subject: Re: Research Subjects drawn randomly from databases
> >
> > I rewrote my line using your suggestion ...
> >
> >  select id,first,middle,last from persons order by rand() limit 10;
> >
> > and it worked perfectly. I'm still curious about why my
> > original version
> > gave such cockeyed results, but I'll focus on the successful solution
> > and leave that unsolved problem for another day. Thank you for your
> > solution Michael.
> >
> > On Sun, 2007-04-29 at 08:48 -0400, Michael Dykman wrote:
> > > If  might suggest:
> > >
> > > SELECT * FROM BAR
> > > ORDER BY RAND()
> > > LIMIT 10
> > >
> > > On 4/29/07, John Kebbel <kebbelj@stripped> wrote:
> > > > For possible educational research purposes, I was playing
> > around with a
> > > > query that would randomly select people from a database.
> > The database I
> > > > experiment with has a group of fictitious persons with id numbers
> > > > (primary key) ranging sequentially from 2 to 378. When I ran these
> > > > queries below, I was expecting to select five random
> > persons from the
> > > > database. The query partially worked. I was getting
> > random subjects, but
> > > > everytime I ran the query, I got a different number of subjects,
> > > > stretching from 0 and up (sometimes as many as 8 or 9). I
> > could see the
> > > > query generating fewer rows if I duplicated an id or made
> > an off-by-one
> > > > error, but I don't see how it could generate more than
> > five. Does anyone
> > > > see my error? (I've used two equivalent forms for the
> > query below; both
> > > > did the same thing)
> > > >
> > > > select id,first,middle,last from persons where id =
> > ceil(rand()*377+1)
> > > > or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id =
> > > > ceil(rand()*377+1) or id = ceil(rand()*377+1);
> > > >
> > > >  select id,first,middle,last from persons where id  in
> > (ceil(rand()*377
> > > > +1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1),
> > > > ceil(rand()*377+1));
> > > > +------+-------------+--------+----------+
> > > > | id   | first       | middle | last     |
> > > > +------+-------------+--------+----------+
> > > > |   35 | Viridiana   | W      | McCarthy |
> > > > |   47 | Crystal     | O      | Cassady  |
> > > > |   67 | Ricardo     | L      | Johnson  |
> > > > |  183 | Christopher | E      | Denver   |
> > > > |  237 | Christopher | B      | Brenner  |
> > > > |  255 | Danielle    | W      | Nickels  |
> > > > |  299 | Christine   | D      | Dexter   |
> > > > |  300 | Rachel      | J      | Baker    |
> > > > |  339 | Jenna       | O      | Murray   |
> > > > +------+-------------+--------+----------+
> > > > 9 rows in set (0.00 sec)
> > > >
> > > >
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> > > >
> > > >
> > >
> > >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> >
> >
> 
> 
> 
> 

Thread
Research Subjects drawn randomly from databasesJohn Kebbel29 Apr
  • Re: Research Subjects drawn randomly from databasesMichael Dykman29 Apr
    • Re: Research Subjects drawn randomly from databasesJohn Kebbel29 Apr
      • RE: Research Subjects drawn randomly from databasesJerry Schwartz30 Apr
        • RE: Research Subjects drawn randomly from databasesJohn Kebbel30 Apr
          • RE: Research Subjects drawn randomly from databasesJerry Schwartz1 May