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
>
>
