List:General Discussion« Previous MessageNext Message »
From:John Kebbel Date:April 29 2007 11:54am
Subject:Research Subjects drawn randomly from databases
View as plain text  
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)


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