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

[Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>]

Στις Παρασκευή 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