Unexpected sort order.

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unexpected sort order.
Date: 2006-11-27 20:44:27
Message-ID: ekfin7$2usr$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

Shouldn't the results of this query shown here been sorted by "b" rather than by "a"?

I would have thought since "order by b" is in the outer sql statement it would have
been the one the final result gets ordered by.

li=# select * from (select (random()*10)::int as a, (random()*10)::int as b from generate_series(1,10) order by a) as x order by b;
a | b
---+----
0 | 8
1 | 10
3 | 4
4 | 8
5 | 1
5 | 9
6 | 4
6 | 5
8 | 4
9 | 0
(10 rows)

Changing the constant from 10 to 11 in either but not both of the
places produces results I would have expected; as do many other ways of
rewriting the query.

Unless I'm missing something, it seems the way I wrote the query creates
some confusion of which of the two similar expressions with random()
it's sorting by.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jeff Davis 2006-11-27 21:44:22 Re: Unexpected sort order.
Previous Message Pete Deffendol 2006-11-27 20:28:08 BUG #2787: postgresql-jdbc-8.1.407 won't install on RHEL4

Browse pgsql-general by date

  From Date Subject
Next Message Ron Johnson 2006-11-27 20:52:36 Re: IS it a good practice to use SERIAL as Primary Key?
Previous Message Scott Ribe 2006-11-27 20:38:00 Re: IS it a good practice to use SERIAL as Primary Key?