pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstrange result for select * from table where id=random()*900
4+ messages / 4 participants
[nested] [flat]
* strange result for select * from table where id=random()*900
@ 2007-07-27 07:48 Stefan Zweig <stefanzweig1881@web.de>
2007-07-27 07:56 ` Re: strange result for select * from table where id=random()*900 Tommy Gildseth <tommy.gildseth@usit.uio.no>
2007-07-27 08:12 ` Re: strange result for select * from table where id=random()*900 Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-07-27 09:23 ` Re: strange result for select * from table where id=random()*900 Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 3 replies; 4+ messages in thread
From: Stefan Zweig @ 2007-07-27 07:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: strange result for select * from table where id=random()*900
2007-07-27 07:48 strange result for select * from table where id=random()*900 Stefan Zweig <stefanzweig1881@web.de>
@ 2007-07-27 07:56 ` Tommy Gildseth <tommy.gildseth@usit.uio.no>
2 siblings, 0 replies; 4+ messages in thread
From: Tommy Gildseth @ 2007-07-27 07:56 UTC (permalink / raw)
To: Stefan Zweig <stefanzweig1881@web.de>; +Cc: pgsql-sql
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 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.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: strange result for select * from table where id=random()*900
2007-07-27 07:48 strange result for select * from table where id=random()*900 Stefan Zweig <stefanzweig1881@web.de>
@ 2007-07-27 08:12 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 0 replies; 4+ messages in thread
From: Achilleas Mantzios @ 2007-07-27 08:12 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: strange result for select * from table where id=random()*900
2007-07-27 07:48 strange result for select * from table where id=random()*900 Stefan Zweig <stefanzweig1881@web.de>
@ 2007-07-27 09:23 ` Kristo Kaiv <kristo.kaiv@skype.net>
2 siblings, 0 replies; 4+ messages in thread
From: Kristo Kaiv @ 2007-07-27 09:23 UTC (permalink / raw)
To: Stefan Zweig <stefanzweig1881@web.de>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-07-27 09:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-27 07:48 strange result for select * from table where id=random()*900 Stefan Zweig <stefanzweig1881@web.de>
2007-07-27 07:56 ` Tommy Gildseth <tommy.gildseth@usit.uio.no>
2007-07-27 08:12 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-07-27 09:23 ` Kristo Kaiv <kristo.kaiv@skype.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox