This isn't terribly clear in the documentation.
But it seems, in 8.0.3 and 8.1.1 that you cannot use a column alias
in an order by if you are using an expression (ie order by
my_column_alias <> 0 asc). If you use a plain asc/desc order by then
it is fine (ie order by my_column_alias asc).
Example:
create table tab1 ( x int );
insert into tab1(x) values (0);
insert into tab1(x) values (0);
insert into tab1(x) values (0);
insert into tab1(x) values (1);
insert into tab1(x) values (2);
insert into tab1(x) values (3);
insert into tab1(x) values (4);
insert into tab1(x) values (5);
-- order by column alias, single table. good.
select x as taco from tab1 order by taco desc;
-- order by x descending, with all zeros at end, using column name
select x as taco from tab1 order by x <> 0 desc, x desc;
-- same as above, but use alias
select x as taco from tab1 order by taco <> 0 desc, taco desc;
-- cleanup
drop table tab1;
Output:
CREATE TABLE
INSERT 423464319 1
INSERT 423464320 1
INSERT 423464321 1
INSERT 423464322 1
INSERT 423464323 1
INSERT 423464324 1
INSERT 423464325 1
INSERT 423464326 1
taco
------
5
4
3
2
1
0
0
0
(8 rows)
taco
------
5
4
3
2
1
0
0
0
(8 rows)
ERROR: column "taco" does not exist
DROP TABLE
thanks
--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/