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

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: strange result for select * from table where id=random()*900
Date: 2007-07-27 08:12:12
Message-ID: 200707271112.12985.achill@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Kristo Kaiv 2007-07-27 09:23:22 Re: strange result for select * from table where id=random()*900
Previous Message Tommy Gildseth 2007-07-27 07:56:20 Re: strange result for select * from table where id=random()*900