Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 03A619FA384 for ; Fri, 27 Jul 2007 06:23:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 14067-01 for ; Fri, 27 Jul 2007 06:23:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp-out.neti.ee (mail.neti.ee [194.126.101.114]) by postgresql.org (Postfix) with ESMTP id C6E1C9FA3AA for ; Fri, 27 Jul 2007 06:23:38 -0300 (ADT) Received: from smtp-out.neti.ee (unknown [88.196.174.136]) by HOT-Bounce1.estpak.ee (Postfix) with ESMTP id 2924761FE11 for ; Fri, 27 Jul 2007 12:23:36 +0300 (EEST) Received: from localhost (localhost [127.0.0.1]) by MXR-5.estpak.ee (Postfix) with ESMTP id 675C61D63A4; Fri, 27 Jul 2007 12:23:27 +0300 (EEST) X-Virus-Scanned: by amavisd-new-2.4.3 (20060930) (Debian) at neti.ee Received: from smtp-out.neti.ee ([127.0.0.1]) by localhost (MXR-2.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id kFUW85jDL5ya; Fri, 27 Jul 2007 12:23:24 +0300 (EEST) Received: from Relayhost2.neti.ee (Relayhost2 [88.196.174.142]) by MXR-5.estpak.ee (Postfix) with ESMTP id 0FBA71D66E1; Fri, 27 Jul 2007 12:23:24 +0300 (EEST) In-Reply-To: <156384696@web.de> References: <156384696@web.de> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: multipart/alternative; boundary=Apple-Mail-1-1062572156 Message-Id: Cc: pgsql-sql@postgresql.org From: Kristo Kaiv Subject: Re: strange result for select * from table where id=random()*900 Date: Fri, 27 Jul 2007 12:23:22 +0300 To: Stefan Zweig X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/168 X-Sequence-Number: 28903 --Apple-Mail-1-1062572156 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On 27.07.2007, at 10:48, 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 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? > > 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 SELECT * FROM _g2977 g, (SELECT floor(random() * (900) as rnd) r WHERE g.id = r.rnd should work better, haven't tested though. Kristo Kaiv http://kaiv.wordpress.com (PostgreSQL blog) --Apple-Mail-1-1062572156 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1
On 27.07.2007, at = 10:48, Stefan Zweig wrote:

hi list,

this is my query:

=A0
*
from _g2977=A0
=A0
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?

i appreciate any help.

thanks in = advance, stefan
In 5 Schritten zur eigenen Homepage. Jetzt = Domain sichern und gestalten!=A0

---------------------------(end of = broadcast)---------------------------
TIP 5: = don't forget to increase your free space map settings
=
SELECT * FROM _g2977 g,
(SELECT = floor(random() * (900) as rnd) r
WHERE g.id =3D = r.rnd

should = work better, haven't tested though.

Kristo = Kaiv
http://kaiv.wordpress.com = (PostgreSQL blog)

 =

= --Apple-Mail-1-1062572156--