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)

If might suggest:

SELECT * FROM BAR
ORDER BY RAND()
LIMIT 10

On 4/29/07, John Kebbel <kebbelj@scripting-solutions.com> 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=mdykman@gmail.com
>
>


--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

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@scripting-solutions.com> 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=mdykman@gmail.com
> >
> >
>
>

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@scripting-solutions.com]
> 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@scripting-solutions.com> 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=mdykman@gmail.com
> > >
> > >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>

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@scripting-solutions.com]
> > 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@scripting-solutions.com> 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=mdykman@gmail.com
> > > >
> > > >
> > >
> > >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
> >
> >
>
>
>
>

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:kebbelj@scripting-solutions.com]
> 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:kebbelj@scripting-solutions.com]
> > > 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@scripting-solutions.com> 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=mdykman@gmail.com
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
> > >
> > >
> >
> >
> >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...e-infoshop.com
>
>