CODEWITHSUNDEEP
mysqlsqlpostgresqlrandom
DrTom
DrTom

Reputation: 103

Random Row in MySQL

I am trying to get a random row in MySQL. Here is my approach: 

SELECT * 
FROM users 
WHERE id = 
 (SELECT floor((max(id) - min(id) + 1) * rand()) + min(id)
  FROM users);

This can return an empty set when there are gaps in the ids. I am fine with that. However,

  1. I don't have gaps and I still get an empty set rather frequently.
  2. More disturbing: I will get two or more results every once in a while.

What is the reason for this strange behavior? How, do I get it right?

Note:

Upvotes: 3

Views: 538

Answers (5)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

This avoids repeated evaluation of random() during the aggregation and should be faster, too, as min(id) and random() are only evaluated once.

SELECT u.* 
FROM   users u
      ,(SELECT min(id) AS min_id, max(id) AS max_id FROM users) x
WHERE  u.id > (floor((x.max_id - x.min_id + 1) * rand()) + x.min_id)
LIMIT  1;

Upvotes: 1

user359040
user359040

Reputation:

I suspect that this is because RAND() is being evaluated for every row in the USERS table in the main query. I suggest:

SELECT u1.* 
from users u1,
     (SELECT floor((max(id) - min(id) + 1) * rand())  + min(id) lid from users) u2
WHERE u1.id > u2.lid
LIMIT 1

Upvotes: 2

Devart
Devart

Reputation: 121932

Try this one -

SELECT t.* FROM
  users t,
  (SELECT @id := (FLOOR((MAX(id) - MIN(id) + 1) * RAND()) + MIN(id)) FROM users) t2
WHERE
  t.id = @id;

Upvotes: 1

Gustav Bertram
Gustav Bertram

Reputation: 14911

How about:

SELECT * 
FROM users 
WHERE id >= (SELECT floor((max(id) - min(id) - 1) * rand())  + min(id) from users)
LIMIT 1

or similar?

Pretty much it should limit you to one result, and if it hits a gap (somehow), then it'll just take the next available one.

Upvotes: 0

Jim H.
Jim H.

Reputation: 5579

SELECT * FROM users ORDER BY rand() limit 1;

Will return a random row.

Upvotes: 0

Related Questions