Well, then color me baffled. I don't know why your first query didn't work.
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:[EMAIL PROTECTED] > Sent: Monday, April 30, 2007 2:42 PM > To: MySQL > Subject: RE: Research Subjects drawn randomly from databases > > 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:[EMAIL PROTECTED] > > > 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
