Skip site navigation (1) Skip section navigation (2)

Order by, expressions & column aliases issue

From: Jeff Trout <threshar(at)torgo(dot)978(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Order by, expressions & column aliases issue
Date: 2005-12-30 15:30:58
Message-ID: 2C195DFC-D4A6-4110-9A49-F7177079D1C9@torgo.978.org (view raw or flat)
Thread:
Lists: pgsql-general
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(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



Responses

pgsql-general by date

Next:From: Ken WinterDate: 2005-12-30 16:33:13
Subject: Re: In processing DDL, when does pg_catalog get updated?
Previous:From: John DeSoiDate: 2005-12-30 15:02:49
Subject: Re: Forum Software

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group