Row Selection Randomization!!

Anon

Hi,

I have a problem with row selection from the oracke db, the reason being that I want to have the row ordering random, everytime a page is accessed.

Please can anyone let me know how can I randomly select rows in an sql query?

Regards,
Nilesh

Anon

i am not sure but try this mysql command:

SELECT * FROM table_name WHERE .... ORDER BY RAND()

Anon

Does it need some settings to run? I am getting an error while running this.

Regards,

[deleted]

Ofcourse you get an error, this is a MySQL-only thing.

You can always cheat:
Select all record-id's from the table.
Load them into an array in PHP.
generate a random number in PHP.
Choose a random recordid from the array using the random number.
Fetch the record with that ID from the table.

Anon

Try this. PHP has a shuffle array function.
Load all keys into an array.

shuffle(array);
select * from m_table where id = array[0];

[deleted]

a valid suggestion, but why make PHP re-order the entire array when you only need to get one random value?
If you benchmark it I bet you'll find that generating a random number and picking using that as an index for the array is a lot faster than shuffling the entire array and then picking the first one.

It's a bit like dengbej's first suggestion that mysqwl uses: reorder the entire table based on random numbers, then select everything and return only the first record.

A note to explain the obvious:
for those of you who are wondering why we don't just select the lowest and highest value from the primary-key column and generate a random number between those two, that's not possible because the sequence may have gaps in it, and that would mean that we might have to do it several times before we get a correct answer.

Anon

Best way I've found is to get a count of all the rows (i.e. select count(*) from table) which is usually much faster than getting every key. Then generate a random number from 0 to count-1 and use a select statement like so:

select * from table limit 1 offset $randomnumber;

Note that i think Oracle still uses the limit (1,$randomnumber) syntax or something like it, which is non-SQL92/99 compliant, but since most of Oracle is non Ansi SQL compliant, this should come as no great surprise. :-)

[deleted]

Note that at least on MySQL and PostgreSQL, using only the limit will cause a sequencial scan, which will slow you down significantly on large tables.