Thread: strange result for select * from table where id=random()*900

hi list,

this is my query:

select 
*
from _g2977 
where 
id=floor(random() * (900));

in table _g2977 i have id(s) from 1 up to 900. i just want to select one random dataset by the above query but i does
notwork.
 

actually i get sometime zero, sometimes one, sometimes two and sometimes even three results back from the above query
althoughi thought it should give only one random dataset from the table.
 

what is wrong?

i appreciate any help.

thanks in advance, stefan
_________________________________________________________________________
In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten! 
Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114

On 07/27/2007 09:48 AM, Stefan Zweig wrote:
> hi list,
> 
> this is my query:
> 
> select 
> *
> from _g2977 
> where 
> id=floor(random() * (900));
> 
> in table _g2977 i have id(s) from 1 up to 900. i just want to select one random dataset by the above query but i does
notwork.
 
> 
> actually i get sometime zero, sometimes one, sometimes two and sometimes even three results back from the above query
althoughi thought it should give only one random dataset from the table.
 


random() isn't immutable, so it's re-calculated for every row. Try f.ex 
this instead:
SELECT
*
FROm _g2977
ORDER BY random()
LIMIT 1


-- 
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

Στις Παρασκευή 27 Ιούλιος 2007 10:48, ο/η Stefan Zweig έγραψε:
> hi list,
>
> this is my query:
>
> select
> *
> from _g2977
> where
> id=floor(random() * (900));
>
> in table _g2977 i have id(s) from 1 up to 900. i just want to select one
> random dataset by the above query but i does not work.
>
> actually i get sometime zero, sometimes one, sometimes two and sometimes
> even three results back from the above query although i thought it should
> give only one random dataset from the table.
>
> what is wrong?

A lot of things seem wierd:

a) Why do you want such a query for?
b) Is "id" a UNIQUE KEY?
If yes, you should get ONE or ZERO rows from the above query.
If not, you should get a number of rows according to the number of rows in the
table with this specific "id" value.
c) why returning (as you put it in the first place) sometimes 1,2,3 rows is
different than returning a "random" dataset?

If "id" was a PRIMARY KEY, maybe it would have sense to do smth like
select * from table where id between floor(random() * 900) and floor(random() * 900);
(note this might return zero rows as well).
Note also that by random, we could very well mean the empty set, so there is
no problem with the above.

But, i feel from your text, that something is highly misunderstood, especially
the way you set the question.
>
> i appreciate any help.
>
> thanks in advance, stefan
> _________________________________________________________________________
> In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten!
> Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Achilleas Mantzios