create table test1 (id integer, sortable_name varchar);
create table test2 (id integer, test1_id integer);
select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name;
ERROR: ORDER BY "sortable_name" is ambiguous
LINE 1: ...t1 inner join test2 on test1.id=test1_id order by sortable_n...
All of these work:
select test1.sortable_name, sortable_name from test1 order by sortable_name /* no join */
select test1.sortable_name, sortable_name from test1, test2 where test1.id=test1_id order by sortable_name; /* implicit join */
select test1.sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* only one sortable_name in select */
select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by test1.sortable_name /* qualify sortable_name in order, but not select */
select test1.sortable_name, test1.sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* qualify sortable_name both selects */
select sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* unqualified everywhere */
select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by substring(sortable_name,1,2); /* use a function in the order */
I've tried this on 9.1.4, 9.1.9, and 9.2.3.
The actual application usage looks more like SELECT users.*, sortable_name FROM users <single join and multiple where clauses> ORDER BY sortable_name. The application code always appends sortable_name to the select list because, depending on available features, sortable_name might be a function call and in a GROUP BY.
Thanks for any insight,
Cody Cutrer