Gossamer Forum: General: Databases and SQL: RAND()

Nov 25, 2002, 3:59 AM

Andy

Veteran / Moderator (17297 posts)

Nov 25, 2002, 3:59 AM

Post #1 of 16

Shortcut

RAND()

With the following query;

SELECT * FROM advertiser_info WHERE CreditLeft >= 0.5 ORDER BY RAND() LIMIT 1

If the RAND() function was not enabled on that version of MySQL, would it yeild an error, or just ignor it? For some resone, when I run this command, it ALWAYS returns the same result. I know definatly there are 3 results in the table...but only one of them ever seems to get chosen Unsure

Any ideas?

Cheers

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

Nov 25, 2002, 10:56 AM

Stealth

Veteran (17240 posts)

Nov 25, 2002, 10:56 AM

Post #2 of 16

Shortcut

Re: [Andy] RAND() In reply to

Don't you have to include the column that you want to randomly order? Not sure about MySQL, but in other database application servers, you have to specify the column name.
========================================
Buh Bye!

Cheers,
Me

Nov 26, 2002, 2:03 AM

Andy

Veteran / Moderator (17297 posts)

Nov 26, 2002, 2:03 AM

Post #3 of 16

Shortcut

Re: [Stealth] RAND() In reply to

That still doesn't seem to be working Unsure

SELECT * FROM advertiser_info WHERE CreditLeft >= 0.5 ORDER BY RAND(PrimaryID) LIMIT 1

Now gives a different result to;

SELECT * FROM advertiser_info WHERE CreditLeft >= 0.5 ORDER BY RAND() LIMIT 1

but it keeps giving the same one with refreshes... Frown

Any ideas?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Nov 26, 2002, 10:55 AM

cornball

User (238 posts)

Nov 26, 2002, 10:55 AM

Post #4 of 16

Shortcut

Re: [Andy] RAND() In reply to

Hello Andy,

Depending on your MySql version [ tested on 3.23 ] try:

SELECT * FROM advertiser_info WHERE CreditLeft >= 0.5 order by rand(now()) limit 1

Reason -> rand(N) where N is the seed for the random sequence, same seed = same result.

using now() to seed seems to produce a random result as now() is constantly changing.

thanks

cornball

Nov 27, 2002, 1:26 AM

Andy

Veteran / Moderator (17297 posts)

Nov 27, 2002, 1:26 AM

Post #5 of 16

Shortcut

Re: [cornball] RAND() In reply to

This is mad. That query is still giving me the same result!

I run it via PHPMyAdmin, but changed it to limit 3, and it brought up 3 results, as I would have expected....but WHY is it not randomising the grabbing of it when only calling one!

BTW They are using the latest build of MySQL.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Nov 27, 2002, 11:47 AM

Alex

Administrator (9386 posts)

Nov 27, 2002, 11:47 AM

Post #6 of 16

Shortcut

Re: [Andy] RAND() In reply to

Must be something in your code, as it works in mysql:

Code:
mysql> create table rand_test ( a int ); 
Query OK, 0 rows affected (0.03 sec) 

mysql> insert into rand_test values (1), (2), (3), (4), (5); 
Query OK, 5 rows affected (0.00 sec) 
Records: 5  Duplicates: 0  Warnings: 0 

mysql> select * from rand_test order by rand() limit 3; 
+------+ 
| a    | 
+------+ 
|    3 | 
|    5 | 
|    1 | 
+------+ 
3 rows in set (0.00 sec) 

mysql> select * from rand_test order by rand() limit 3; 
+------+ 
| a    | 
+------+ 
|    1 | 
|    2 | 
|    4 | 
+------+ 
3 rows in set (0.00 sec) 

mysql> select * from rand_test order by rand() limit 3; 
+------+ 
| a    | 
+------+ 
|    3 | 
|    4 | 
|    5 | 
+------+ 
3 rows in set (0.00 sec) 

mysql> select version(); 
+-----------+ 
| version() | 
+-----------+ 
| 3.23.51   | 
+-----------+ 
1 row in set (0.00 sec)

Try reproducing the above manually and see if it works for you on their mysql server.

Cheers,

Alex
--
Gossamer Threads Inc.

Nov 27, 2002, 11:49 AM

Alex

Administrator (9386 posts)

Nov 27, 2002, 11:49 AM

Post #7 of 16

Shortcut

Re: [cornball] RAND() In reply to

Quote:

using now() to seed seems to produce a random result as now() is constantly changing.

You shouldn't need to seed rand(), if not called with arguments, it should be sufficently randomly seeded as to not give duplicate results. If you use now(), two queries in the same second will have the same random order.

Cheers,

Alex
--
Gossamer Threads Inc.

Nov 27, 2002, 12:33 PM

cornball

User (238 posts)

Nov 27, 2002, 12:33 PM

Post #8 of 16

Shortcut

Re: [Alex] RAND() In reply to

Hello Alex,

Yes I agree I should not have to use now().

But the random function does not seem to work with a very limiting where condition.

As in : Select * from rand_test where a < 4 order by rand() limit 3

If there are only 3 matching records for the condition then order by rand() does not appear to work.

I get a result of 3,2,1 for the above query every time with MySql version 3.23.

Could I not expect 1,2,3 or 2,3,1 or 3,2,1 or 2,1,3 or 1,3,2 or 3,1,2 as possible results ?

Thanks

cornball

Last edited by:

cornball: Nov 27, 2002, 12:34 PM

Nov 27, 2002, 12:46 PM

Alex

Administrator (9386 posts)

Nov 27, 2002, 12:46 PM

Post #9 of 16

Shortcut

Re: [cornball] RAND() In reply to

Really? I get random results:

Code:
mysql> select * from rand_test where a < 4 order by rand() limit 3; 
+------+ 
| a    | 
+------+ 
|    1 | 
|    2 | 
|    3 | 
+------+ 
3 rows in set (0.00 sec) 

mysql> select * from rand_test where a < 4 order by rand() limit 3; 
+------+ 
| a    | 
+------+ 
|    2 | 
|    1 | 
|    3 | 
+------+ 
3 rows in set (0.00 sec) 

mysql>

What version are you using (do a select version()).

Cheers,

Alex
--
Gossamer Threads Inc.

Nov 27, 2002, 1:19 PM

cornball

User (238 posts)

Nov 27, 2002, 1:19 PM

Post #10 of 16

Shortcut

Re: [Alex] RAND() In reply to

Hello Alex

Select version() returns 3.23.53-max-nt which I am using on Win XP.

Could it be a Windows specific rand() undocumented security feature ?

rand() works fine if I do not put such a restrictive where a < 4 ,

as in where a < 6 works fine.

Thanks

cornball

Nov 27, 2002, 1:47 PM

Wil

Veteran / Moderator (4108 posts)

Nov 27, 2002, 1:47 PM

Post #11 of 16

Shortcut

Re: [cornball] RAND() In reply to

http://groups.google.co.uk/...e=UTF-8&oe=UTF-8

- wil

Nov 28, 2002, 2:05 AM

Andy

Veteran / Moderator (17297 posts)

Nov 28, 2002, 2:05 AM

Post #12 of 16

Shortcut

Re: [Alex] RAND() In reply to

Thansk guys. I've emailed the server owner where I am having the problem, and I'm going to check if the newest version of MySQL was compiled correctly...because both me, and all of you, seem to think that this code should work. I tried the SQL query from PHPMyAdmin, so thats as good as running in Telnet (isnt it?).

Cheers

Andy (mod)
andy@ultranerds.co.uk

Jan 23, 2003, 7:20 AM

SpeedStreet

New User (1 post)

Jan 23, 2003, 7:20 AM

Post #13 of 16

Shortcut

Re: [Andy] RAND() In reply to

Actually, you are not alone.

I've been trying to tweak a line, and just recently became aware of the usage of now() within RAND(). But, it still only seems to be pulling one record consistently out of the three I am using for my test.

Here's the line:

$advert=$DB_site->query_first("SELECT * FROM ads WHERE wholeforum='1' AND time>=".time()." AND (exposed<=exposures OR exposures=0) ORDER BY RAND(NOW())");

Now, for all intents and purposes, this should pull a random ad each refresh, no? What would be causing it not to work?

Thanks in advance for any help anyone can give me?

Additional Info:

MySQL Version: 3.23.54
PHP Version: 4.2.3
OS: Linux

Feb 4, 2003, 9:15 PM

neb_greely

New User (1 post)

Feb 4, 2003, 9:15 PM

Post #14 of 16

Shortcut

Re: [SpeedStreet] RAND() In reply to

In Reply To:

Actually, you are not alone.

I ditto that. The only difference with me is that it seemed to stop working suddenly. I had it working just fine with the code I was using:

select * from stories where story_status = 1 order by RAND() limit 1

a very simple query. And it had been reliably returning a random result. Actually, it continues to return a random result on my box:
mac os x/MySQL 3.23.51

but the same code doesn't work on my host.
Linux/3.23.54

I've done a number of searches and found other people having the same problem.

Any suggestions are most welcome.
-Ben

Apr 19, 2003, 11:38 PM

Annelid

New User (1 post)

Apr 19, 2003, 11:38 PM

Post #15 of 16

Shortcut

Re: [Andy] RAND() In reply to

i have an idea that this problem may be because ORDER BY technically takes a column for parameter. perhaps certain builds of the newest versions of mySQL are interpreting the rand to mean choose a random column... in which case no randomization of rows occurs, and the first result is always returned.

just a novice's idea... i encountered this problem in my randomize php script about 5 minutes ago.

Jul 30, 2003, 6:50 AM

faction

New User (1 post)

Jul 30, 2003, 6:50 AM

Post #16 of 16

Shortcut

Re: [SpeedStreet] RAND() In reply to

I've been having the same problem with ORDER BY RAND. Seeding at, as the MySQL.com doc page suggests did not seem to help at all.

One solution (though, I will warn you up front, it is a hack) that has been working great for me is to use the MySQL REVERSE command on the result of the RAND(). This works because the least significant digits RAND() returns do change every time, while the more significant digits do not. So, your query would look something like:

Code:
SELECT blah, REVERSE(RAND(NOW())) AS rnd FROM table ORDER BY rnd

Works good for me, and is significantly faster than the MD5 suggestion mentioned elsewhere.