Chris Browne wrote: > If I replicate your query, with extra columns, AND NAMES, I get the following: > > [EMAIL PROTECTED]:5433=# select random() as r1, random() as r2, random() as > r3 from generate_series(1,10) order by random(); > r1 | r2 | r3 > --------------------+--------------------+------------------- > 0.0288224648684263 | 0.904462072532624 | 0.27792159980163 > 0.144174488261342 | 0.406729203648865 | 0.452183415647596 > ... > It is indeed somewhat curious that the query parser chose to interpret > that the "order by random()" was referring to column #1.
And even more curiously, IMHO, even specifying column names isn't enough. Note that this: 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) is sorted by "a" even though the outermost "order by" clause explicitly said to order by "b". Seems like it's a known odd behavior ... http://archives.postgresql.org/pgsql-general/2006-11/msg01523.php http://archives.postgresql.org/pgsql-general/2006-11/msg01539.php http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
