PostgreSQL Discussion - Order by, expressions & column aliases issue

Author

Order by, expressions & column aliases issue

Jeff Trout

2005-12-30, 11:23 am

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/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

John D. Burger

2005-12-30, 1:23 pm

Jeff Trout wrote:

> 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).

Yah - I ran into this a few months ago. Here's Tom's comments:

http://archives.postgresql.org/pgsq...10/msg00208.php

- John Burger
MITRE

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links