From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | andrew(at)grillet(dot)co(dot)uk |
Cc: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Functions in sort order - undocumented restriction |
Date: | 2018-02-10 18:56:37 |
Message-ID: | 5183.1518288997@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
=?utf-8?q?PG_Doc_comments_form?= <noreply(at)postgresql(dot)org> writes:
> This does not work:
> select distinct (o.bid), organisation, posttown, replace(case when postcode
> ='' then null else trim(postcode) end, ' ', '') as pc, phone, o.active,
> website, email, (select count(*) from notes as n where n.bid = o.bid) as nn
> from organisations as o right join notes as n on o.bid = n.bid where true
> order by replace(case when phone ='' then null else trim(phone) end, ' ',
> '') nulls last ;
> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
What do you find unclear about this error message?
The reason seems clear enough to me: if the ordering expression isn't one
of the values that are being de-duplicated on, then there isn't a unique
value to associate with each surviving row for sorting purposes.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-02-10 19:06:00 | Re: Documentation of EXCEPT ALL may have a bug |
Previous Message | David G. Johnston | 2018-02-10 15:26:13 | Re: Functions in sort order - undocumented restriction |