Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Random row selection

Random row selection

Author: rootsql Date: 11-11-2011, 22:20

Table creation:

CREATE TABLE table_random(
  value INT(11) NOT NULL UNIQUE KEY
);
INSERT INTO table_random VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

First variant is based on random ordering, which can be very slow on big tables:

SELECT value FROM table_random
  ORDER BY RAND()  
  LIMIT 1;

 

Next variant generates random number that is used in WHERE clause to select the row:

SELECT t1.value FROM table_random t1,
  (SELECT @value := (FLOOR((MAX(value) - MIN(value) + 1) * RAND()) + MIN(value)) FROM table_random) t2
WHERE t1.value = @value;

This query works when table has no gaps in values.

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.