MySQL – returning random row


[adsense id=”0514458240″ width=”468″ height=”60″]

This is mini example of how to return a random row from your table:

mysql> select * from t1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| ronan4 |  100 |  115 | 
| ronan4 |  155 |  116 | 
| ronan4 |  225 |  250 | 
| ronan4 |  300 |  275 | 
| ronan4 |  325 |  350 | 
| ronan4 |    1 |    2 | 
| ronan4 |    1 |    3 | 
| t13    |    7 |    6 | 
+--------+------+------+
8 rows in set (0.00 sec)

Col2 is a primary or unique key to the table. If we issue the following statement:

mysql> select * from t1 where col2 >= (select floor(max(col2) * rand()) from t1)
    -> order by col2
    -> limit 1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| ronan4 |    1 |    3 | 
+--------+------+------+
1 row in set (0.01 sec)

mysql> select * from t1 where col2 >= (select floor(max(col2) * rand()) from t1) order by col2 limit 1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| ronan4 |  155 |  116 | 
+--------+------+------+
1 row in set (0.01 sec)

mysql> select * from t1 where col2 >= (select floor(max(col2) * rand()) from t1) order by col2 limit 1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| ronan4 |    1 |    2 | 
+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from t1 where col2 >= (select floor(max(col2) * rand()) from t1) order by col2 limit 1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| ronan4 |  100 |  115 | 
+--------+------+------+
1 row in set (0.00 sec)

mysql> select * from t1 where col2 >= (select floor(max(col2) * rand()) from t1) order by col2 limit 1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| ronan4 |  225 |  250 | 
+--------+------+------+
1 row in set (0.00 sec)

Issuing the statement several times show different results each time.
[googleplus]


One response to “MySQL – returning random row”

  1. Just add this to your WHERE clause:

    AND RAND() < 0.1 (or use WHERE RAND() < 0.1 if you have no WHERE clause)

    This will give you 1/10 of the records at random, with a different result each time the query is run.

    Replacing 0.1 with 0.01 gives 1/100 of the records, etc.