Randomizing results in mysql

Question

I'm making a raffle program and I want the person's name to be random but not repeat any records. I'm selecting between the two queries below. The first one is sure that no record is repeated from the returned result but sacrifices the people with higher id's since they will not be selected. The second one is by random but might select repeated records. Is there any other way I can achieve random records in mysql or in php without repeating any of the returned results?

$select_random = $db->get_results("SELECT person FROM tbl_people WHERE state = 1  LIMIT $offset, 30");

$select_random = $db->get_results("SELECT participant FROM tbl_participants WHERE state = 1 ORDER BY RAND()");

On a side note: `ORDER BY RAND()` is notoriously expensive. If your table grows [this question might be helpful](http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function) — Wrikken, Jan 15 '12 at 14:39

score 5 · Accepted Answer · answered Jan 15 '12 at 14:37

how about adding DISTINCT

$select_random = $db->get_results("SELECT DISTINCT person FROM tbl_people WHERE state = 1  LIMIT $offset, 30");

or

$select_random = $db->get_results("SELECT DISTINCT participant FROM tbl_participants WHERE state = 1 ORDER BY RAND()");

Randomizing results in mysql

1 Answers1