PDA

View Full Version : Select random row



blm126
08-03-2006, 02:06 PM
I have a table called funny that contains a lot of funny quotes. I would like to randomly display 1 on a page.I am doing this right now by loading them all into an array and shuffleing the array.Is their any way to do this with mysql? Here's my sql right now


SELECT data FROM funny WHERE funny.set = 'general'

I'm not very good with sql so any help would be great.

mwinter
08-03-2006, 02:25 PM
I would like to randomly display 1 [result] on a page.I am doing this right now by loading them all into an array and shuffleing the array.Is their any way to do this with mysql?MySQL implements many extensions to the SQL standard (I'd prefer that they finish implementing SQL though, personally), one of which is ordering based on an expression. The rand function can be used to obtain a random order, and the limit clause can speed up the query, as well as reduce the amount of data to transfer:

SELECT data FROM funny WHERE funny.set='general' ORDER BY RAND() LIMIT 1

Be aware that the above is untested, and if you were to move to another DBMS (like PostgreSQL, for instance), this query is likely to fail.

Mike

blm126
08-03-2006, 04:34 PM
Thanks that worked perfectly