Re: select random order by random

From: piotr_sobolewski <piotr_sobolewski(at)o2(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: select random order by random
Date: 2007-11-01 18:37:01
Message-ID: 1c5168f8.3da38756.472a1ccd.c2bed@o2.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thursday 01 November 2007 17:08, brian wrote:

> > I was very surprised when I executed such SQL query (under PostgreSQL
> > 8.2): select random() from generate_series(1, 10) order by random();
> >
> > I don't understand - why the result is like that? It seems like in each
> > row both random()s were giving the same result. Why is it like that? What
> > caused it?
>
> Your query specifically requested that the result be ordered by the
> column "random" in the result set (the default ordering direction being
> ASC). Your query is semantically identical to:
> SELECT random() AS foo FROM generate_series(1, 10) ORDER BY foo ASC;

I also had such theory. But if I do such query:
select x from generate_series(1, 10) as x order by random();
the answer is shuffled in random order.
So why in one case this "random()" is treaded as a column name and in second -
as function name?

And when I do such query:
select random() as xxx, random() from generate_series(1, 10) order by
random();
your theory would predict that the answer is ordered by the second column (as
the first one is renamed to 'xxx'). However in reality the answer is in
random order.

> I should think that you would get a better result if you dropped the
> ORDER BY clause.

Yes, I know. However, once I made such request just for fun and curiosity, and
found that I don't know why does it work like that. And since then I think
about it and try to understand it - if in this case Postgres behaves the way
I don't understand, I probably don't understand it well at all.

--
Piotr Sobolewski
http://www.piotrsobolewski.w.pl

Browse pgsql-general by date

  From Date Subject
Next Message piotr_sobolewski 2007-11-01 18:38:22 Re: select random order by random
Previous Message Tom Lane 2007-11-01 18:27:43 Re: Solaris 10, mod_auth_pgsql2