[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]