MySQL query error

Anon

Consider this table:

col1 col2 category
Tom Raye edu
Joe Dell soc
Pat Kery soc
Wil Star soc
Ali Aman edu
Sue Scot rec

I want to do the following:

RANDOMly select ONE record,
from all categories,
except category edu

I tried the following query, but I kept getting an error 1064:

select * from tbl, WHERE category != "edu" ORDER BY rand() limit 1;

The error message is:

you have an error in your SQL syntax near 'category != "edu" ORDER BY rand() limit 1' at line 1.

Any ideas? Thanks folks.

Richie.

lawnmowerman

Well, you're REAL close. You just have an extra comma.

Change:
select * from tbl, WHERE category != "edu" ORDER BY rand() limit 1;

select * from tbl WHERE category != "edu" ORDER BY rand() limit 1;

-Rich

Anon

I tried without the comma, but i still get the same error messge.

This query works:

select * from tbl where category = 'soc' limit 1;

But this gives an error 1064:

select * from tbl where category = 'soc' order by rand() limit 1;

The error is:
you have an error near 'rand() limit 1'

What am I doing wrong?

eojlin

i'm not really sure... but try not to use <b>semi-colons</b> with your PHP passed query.

Anon

You need to join the random to the query so that it appears as a column, then mysql can order it for you. Best done with the id, you multiple a column by 0 and add a random amount to it. This gives you a random number for each row, mysql orders it by that column.

My example uses

id col1 col2 category
0 Tom Raye edu
1 Joe Dell soc
2 Pat Kery soc
3 Wil Star soc
4 Ali Aman edu
5 Sue Scot rec

$sql =
select col1, col2, category, id*0+rand() as random from tbl, ORDER BY random DESC

should output something like

col1 col2 category random
Tom Raye edu 0.589
Joe Dell soc 0.348
Pat Kery soc 0.286
Wil Star soc 0.027
Ali Aman edu 0.015

You can never order by rand() alone
happy randoming
mike

lawnmowerman

Actually, with MySQL 3.23 and higher, you CAN order by rand().

Check out the rand() portion of: http://www.mysql.com/doc/M/a/Mathematical_functions.html

My only thought as to why my first suggestion didn't work is because you are using a version of MySQL older than 3.23. In which case, Mike's suggestion MAY work.

I cannot say whether it does in all cases or not, but I know that it does not on MySQL 3.23.29-gamma. Mike's query returns the same result each time the query is executed on this version.

-Rich

Anon

Thanks a million guys. Mike's suggestion gave an error because of the last comma.

Here's what ultimately worked:

SELECT col1, col2, category, id+rand() AS shuffle FROM tbl WHERE category != 'edu' ORDER BY shuffle DESC LIMIT 1;

Richie.

http://sabyo.dyns.cx/