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]