I have around 300 records in some table in mysql database. And I have a requirement to fetch 40 random records with one query. How to write the query? need help thanks.
1
-
1stackoverflow.com/search?q=rand+mysql try looking into these topics. Your answer is thereTim– Tim2010-10-23 12:01:02 +00:00Commented Oct 23, 2010 at 12:01
Add a comment
|
6 Answers
For a small table likes yours it should suffice with this:
SELECT * FROM table ORDER BY RAND() LIMIT 40;
Note that this is not suitable for large tables since MySQL will have to do a table scan and order all rows in the table due to the usage of ORDER BY RAND(). For large tables you will have to implement this mostly in application code, keeping track of which rows you've already got and generating random ids to fetch.
Comments
You should use rand() with order by like this:
SELECT field1, field2
FROM tableName
ORDER BY RAND()
LIMIT 40
Comments
order by rand() may cause performance issue, instead try to do in following way:
// what NOT to do:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// much better:
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
Comments
Note that this is not a fast solution, but it works fine for just 300 records
SELECT [rows]
FROM [table]
ORDER BY RAND()
LIMIT 40
Comments
you should user RAND() in the WHERE clause
but you first make fragment 40 / number of rows in table
ex:
SELECT * FROM [TABLE_NAME] WHERE RAND()< 0.0005;
Comments
SELECT * FROM table ORDER BY RAND() LIMIT 40