Okay, so why does wrapping the order by in a function fix it? (or not doing a join, or doing an implicit join)
Cody Cutrer
On Wed, May 22, 2013 at 11:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Cody Cutrer <cody@instructure.com> writes: > 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...
I think it's unhappy because "sortable_name" could refer to either of the output columns (under the old SQL92 convention that an ORDER BY item is an output column name). Probably the easiest way to dodge that is to qualify the name, ie ORDER BY test1.sortable_name. A different line of attack is to use AS to relabel whichever output column you don't want to match.