Getting random rows from a database

Hecate

Is it possible to return (reasonably) random rows, from a database?.

I'm doing this in PHP with a PostgreSQL backend; what I've done so far is this :

# get user ids in order

$q_get_second_level = "SELECT agentID from tms_agents WHERE groupID ='secondlevel' ORDER BY agentID";

$set1 = pg_exec($db, $q_get_second_level);

# rows returned..

$maxrows = pg_numrows($set1);

# get the last user id

for($index = 0; $index < $maxrows ; $index++)
{
$no_secondlevel = pg_fetch_row($set1, $index);
}

# get the first user id..

for ($index = 0; $index < 1; $index++)
{
$secondlevel_lim = pg_fetch_row($set1, $index);
}

# seed a format for the random number generator, format below seems to give most randomness

srand((double)microtime()*1000000);

# select a random user id value between the first and last user id values as the range

$random_user = rand($secondlevel_lim[0], $no_secondlevel[0]);

I *thought* this might work but all it seems to do is generate the last user id as the random value, all the time, so I guess it's not that random

Is there an SQL statement that would do this?

Lemming

Whilst not answering your question directly, I had to use the rand() function in C/C++ in my final year project for college. Problem was that rand() itself is not quite as random as you'd like to think. Therefore, I had to use the systemclock to take an integer to feed to rand() to make the number more random. You might want to try the same thing here??

amen

hmm why do you want to do this ?

the order in which data is retieved from a database depends on several factors
:prescence/abscence of keys/indexs
:order by clauses in your sql
:quanity/methods of updates/inserts

I don't know a lot of PostGre SQL but reading the web site and looking at functions you can use random in SQL.

look at
http://www.postgresql.org/idocs/index.php?functions-math.html

Hecate

well, it seems to be a bit better now, I'm using the mt_rand()function instead and it seems to be returning a pretty acceptable range of values.