Home > mysql, Programming > Play with randomness in MySQL

Play with randomness in MySQL

Some of you might know about the rand() function in MySQL — it randomly generates float point number between 0 and 1. Most of the time we use it to generate results that we want to be in random order. For example,

We have a people table that has the following records:

select * from people;
+-----------+
| name      |
+-----------+
| Bob       |
| Alice     |
| Kim       |
| Tom       |
| Jerry     |
| Linda     |
| Fransisco |
| Zack      |
| Peter     |
+-----------+

We are going to pick 3 persons out of the list randomly, so we do a

mysql> select * from people order by rand() limit 3;
+------+
| name |
+------+
| Zack |
| Bob  |
| Kim  |
+------+

The above query should return (most likely) different result every time. Next I am going to make it a little more interesting. Let’s say I have another table named prize which contains a list of prizes:

select * from prize;
+-----------------+
| name            |
+-----------------+
| Pencil          |
| Coffee grinder  |
| iPad            |
| GPS watch       |
| Yoga mat        |
| 2 movie tickets |
+-----------------+

What we want to do is to assign a randomly picked prize to each person in the people table (assuming the same prize can be assigned to more than one person as there are fewer number of prizes than number of people), here’s the query

select o.*, (select name from prize order by rand() limit 1) as prize from people o;

This would return something like the following (again the result will be most like different if you try it):

+-----------+-----------------+
| name      | prize           |
+-----------+-----------------+
| Bob       | iPad            |
| Alice     | Yoga mat        |
| Kim       | GPS watch       |
| Tom       | GPS watch       |
| Jerry     | iPad            |
| Linda     | GPS watch       |
| Fransisco | Pencil          |
| Zack      | 2 movie tickets |
| Peter     | GPS watch       |
+-----------+-----------------+

For your convenience you can use the following sql statements to generate the tables with data populated:

CREATE TABLE `people` (
  `name` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `people` VALUES ('Bob'),('Alice'),('Kim'),('Tom'),('Jerry'),('Linda'),('Fransisco'),('Zack'),('Peter');

CREATE TABLE `prize` (
  `name` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `prize` VALUES ('Pencil'),('Coffee grinder'),('iPad'),('GPS watch'),('Yoga mat'),('2 movie tickets');
Advertisements
Categories: mysql, Programming
  1. December 28, 2011 at 10:04 am

    You have observed very interesting details! ps nice internet site.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: