I have a query which selects 6 names in the database. eg
SELECT names FROM users LIMIT 6
Now I want this query to select a random 6 names in the database table, is this possible? And how
-
Related: stackoverflow.com/questions/1823306/…OMG Ponies– OMG Ponies2010-05-05 19:23:58 +00:00Commented May 5, 2010 at 19:23
Add a comment
|
3 Answers
The simple but slow solution is:
SELECT names FROM users ORDER BY RAND() LIMIT 6
This uses sorting on a random number and has O(n log n) performace. It should run fine for say 10000 rows, but for larger tables it won't scale well.
To get it faster you can look at Quassnoi's article MySQL: selecting a number of random rows fast.
SELECT *
FROM (
SELECT @cnt := COUNT(*) + 1,
@lim := 6
FROM users
) vars
STRAIGHT_JOIN
(
SELECT names,
@lim := @lim - 1
FROM users r
WHERE (@cnt := @cnt - 1)
AND RAND() < @lim / @cnt
) i
This has O(n) performance.
Comments
SELECT names
FROM users
ORDER BY RAND()
LIMIT 6
Comments
Here is a other quick solution
Select names from users WHERE RAND() LIMIT 6
1 Comment
Ike Walker
This may be fast, but it's not correct. It will give you 6 rows, but they are not random.