SELECT field FROM table ORDER BY RAND() LIMIT 1 !!!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

SELECT field FROM table ORDER BY RAND() LIMIT 1 !!!!

Post by AVATAr »

ok, i search in the forum and there are some answears for this topic, but i cant make it work

i have linux running mysql 3.23.54

i want to search for a random record thats why i use:

Code: Select all

SELECT field1, field2 FROM table ORDER by RAND() Limit 1
i try using this too:

Code: Select all

SELECT field1, field2, RAND(NOW()) as rand_col FROM table ORDER BY rand_col LIMIT 1
Neither work on my linux server, but they do ok over windows!!!!, i supose i have to initialize the RAND??? but the RAND() is in the SQL statement not in the php code?... any clue?

thanks
superwormy
Forum Commoner
Posts: 67
Joined: Fri Oct 04, 2002 9:25 am
Location: CT

Post by superwormy »

What doesn't work about it on Linux?

I know there was actually a bug in the Windows version of MySQL where the first time you did an ORDER BY RAND() it wouldn't give you a random number...
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Post by AVATAr »

it dont randomize! check it out: http://www.avataruniverse.com/montevi

in the up right corner (in white letters) there is a quote... i use the sql statement shown before... no special php, simply that... but it dont randomize it when i reload the browser.

thks
superwormy
Forum Commoner
Posts: 67
Joined: Fri Oct 04, 2002 9:25 am
Location: CT

Post by superwormy »

Try running the query twice, and use the result from teh second query.

Another thought would be to run a query like this:
SHOW TABLE STATUS LIKE 'temp_cloak'

And get the number of rows, then, use PHP to get a random number:
$randomnum = rand (1, $array['Rows']);

Then do your query:
SELECT * FROM tablename LIMIT $randomnum, 1
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

Post by AVATAr »

yep.. i have implemented something like that, but is more efficient to use only 1 query (). :wink: IF exists a solution to my problem).

1) select table_id from table
2) insert those values into an array, randomize them, take 1
3) select * from table where table_id=ID_FROM_ARRAY

but iwant to use only 1 query!!! :cry:
Post Reply