Undefined rand expression ?

[redskycorp]

Hey guys, i don`t understand whats wrong with this sql statement....An y ideas ?

SELECT *
FROM Temp
ORDER BY rand();

[pbmods]

Heya, redskycorp.

Nothing jumps out at me. Maybe enclosing your table name in backticks will help. Also, usually when you ORDER BY RAND(), you tend to also LIMIT:

[code=mysql]
SELECT
*
FROM
`Temp`
ORDER BY
RAND()
LIMIT 1;
[/code]

[redskycorp]

I did this....

SELECT *
FROM `Temp`
ORDER BY rand()
LIMIT 2;

Now it says syntax error (missing operator) in query expression ' ran() LIMIT 2 '....

[dafodil]

Try to put a where statement on it...

For example:

[code=sql]
SELECT *
FROM `pdt_tracker` where `Track_ID`
ORDER BY rand()
LIMIT 2;


[/code]

[redskycorp]

Ok now it getting weird.....This is what i did...

SELECT *
FROM Temp
ORDER BY Rand;

Now it does randomize but it asks me to enter parameter, i don`t want the dialog box to pop-up....so i tried...

SELECT *
FROM Temp
ORDER BY Rand
LIMIT 3;

Now an syntax error missing operator in query expression 'Rand LIMIT 3' appears..... What am i doing wrong ?

[dafodil]

Ok now it getting weird.....This is what i did...

SELECT *
FROM `Temp`
ORDER BY Rand;


Now it does randomize but it asks me to enter parameter, i don`t want the dialog box to pop-up....so i tried...

SELECT *
FROM Temp
ORDER BY Rand
LIMIT 3;

Now an syntax error missing operator in query expression 'Rand LIMIT 3' appears..... What am i doing wrong ?

Just put this:
[code=sql]
SELECT *
FROM `Temp`
ORDER BY Rand();
[/code]
Instead of:

SELECT *
FROM Temp
ORDER BY Rand;


Put a () after Rand and dont forget this `` enclosing the Temp

Cheers

[redskycorp]

SELECT *
FROM `Temp`
ORDER BY Rand();

I did this but i get error undefined Rand in expression..... .how do i define Rand() ?

[dafodil]

SELECT *
FROM `Temp`
ORDER BY Rand();

I did this but i get error undefined Rand in expression..... .how do i define Rand() ?

It seems that the problem is in your sql... That's odd, its ok in my sql...
Maybe you are using an old version of mysql. Try updating it...

[redskycorp]

The thing is i am not using mysql. I am trying to write a query in access, designing query is done using sql codes......

[dafodil]

The thing is i am not using mysql. I am trying to write a query in access, designing query is done using sql codes......

I'm not sure but try to replace Rand() with Random() since there is a Random() function in access...

[redskycorp]

Eureka ! I finally found the solution ! Thanks dafodil, after trying out your codings and experimenting a bit, i got the answer.......

SELECT *
FROM Temp
ORDER BY Rnd(Len([Topic]));

It was just that simple, and here i was knocking my head trying to figure out the answer.....than ks again guys !

[bartonc]

Eureka ! I finally found the solution ! Thanks dafodil, after trying out your codings and experimenting a bit, i got the answer.......

SELECT *
FROM Temp
ORDER BY Rnd(Len([Topic]));

It was just that simple, and here i was knocking my head trying to figure out the answer.....than ks again guys !

Which would have been found much more quickly if you had posted in the relevant forum. Please try to keep your threads in the forum to which they belong (or at least put something like "In Access, how do I...?" so that a moderator can move your thread to where it belongs. Thanks.

[redskycorp]

Sorry bartonc, i thought since the question was more on sql, i thought this thread was more appropriate.... sorry....

[bartonc]

Sorry bartonc, i thought since the question was more on sql, i thought this thread was more appropriate.... sorry....

It's no problem. The thing is that MySQL is not generic SQL and I'd like to see you get answers to your questions in an efficient manner.

Thanks for the reply,
Barton