[Solved] RAND() - Unexpected results

[arfgh]

i did the follow query:

SELECT "ID", "Title" FROM "Movies" WHERE "ID" = ROUND( ( RAND( ) * ( SELECT MAX( "ID" ) FROM "Movies" ) ), 0 )

sometimes there is no result, and sometimes there is a single result in a row, and sometimes there are 2 or 3 results.
Only one random number and one result expected.

How is possible ? did i something bad on the query ? ... the most probable.

[chrisb]

the value returned by your where clause will vary with each row processed (due to rand()).
try this:-

Code: Select all

select ID, "Title"
from
"Movies"

join
(select round(rand() * max(ID), 0) ID from "Movies") "Rand_ID"
on "Rand_ID".ID = "Movies".ID

[arfgh]

yes but if i obtain a single number from rand operation, why to get, sometimes, no results, other times 1, other times 2...... i cant see it

[Villeroy]

SELECT TOP 1 FROM "Movies" ORDER BY RAND()

[arfgh]

villery i didnt understand that query you suggested....

anyways now i understood what chrisb pointed. Each comparison in my query threw a different rand value, for that reason failed or showed unexpected results.
So the way to fix it was to perform a single random value and join it to the main query... i guess....

[Villeroy]

It selects a random record from "Movies".

[arfgh]

ok ok, only works in direct mode
interesting way to do it, anyways

Code: Select all

select top 1 "ID", "Title" from "Movies" order by RAND()

[Sliderule]

ok ok, only works in direct mode
interesting way to do it, anyways

Code: Select all

select top 1 "ID", "Title" from "Movies" order by RAND()

If you insist on running the Query while using the Base Parser ( NOT Direct Mode ) . . . use this syntax instead:

Code: Select all

Select 
   "ID", 
   "Title" 
From "Movies" 
Order By RAND() 
LIMIT 1

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[arfgh]

thanx guys, all of them seems very interesting ways.