SQL Rand Function

The RAND() function is used for returning random numbers. For example, the statement SELECT rand() will return a random number between 0 and 1 as such:

mysql> SELECT rand();
+-------------------+
| rand() |
+-------------------+
| 0.138089752884665 |
+-------------------+
1 row in set (0.00 sec)

This function comes in handy when you are selecting customers to receive a special offer or perhaps raffling off a prize in a sweepskate contest. To show how this function would work, I have designed a sample table called Pie from a MySQL database called Dessert. The rand function will be used to generate a random someValue integer.

SELECT * FROM pie;

pie_id	name	someValue
1	apple	1
2	cherry	4
3	peach	2
4	berry	3
5	cream	5
6	pumpkin	6
7	mince	7
8	rubarb	10
9	pecan	8
10	mud	9

If you write the SQL query: SELECT someValue FROM pie ORDER BY rand() limit 1; you will generate a random someValue integer. When I ran this query 5 times, I got the following values: 2,4,9,4,8.

The following works for MS SQL and Oracle:
MS SQL:
SELECT TOP someValue FROM pie
ORDER BY someValue;

Oracle:
SELECT someValue FROM (
SELECT someValue from pie ORDER BY dbms_random.someValue) WHERE rownum=1;