From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: strange result for select * from table where id=random()*900
Date: Fri, 27 Jul 2007 11:12:12 +0300
User-Agent: KMail/1.9.3
References: <156384696@web.de>
In-Reply-To: <156384696@web.de>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-7"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200707271112.12985.achill@matrix.gatewaynet.com>

=D3=F4=E9=F2 =D0=E1=F1=E1=F3=EA=E5=F5=DE 27 =C9=EF=FD=EB=E9=EF=F2 2007 10:4=
8, =EF/=E7 Stefan Zweig =DD=E3=F1=E1=F8=E5:
> hi list,
>
> this is my query:
>
> select
> *
> from _g2977
> where
> id=3Dfloor(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=20
table with this specific "id" value.
c) why returning (as you put it in the first place) sometimes 1,2,3 rows is=
=20
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)=20
	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 i=
s=20
no problem with the above.

But, i feel from your text, that something is highly misunderstood, especia=
lly=20
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=3D021114
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

=2D-=20
Achilleas Mantzios