Thread: SELECT DISTINCT ... ORDER BY UPPER(column_name) fails

On pgsql 8.2, if you do the following query, everything is fine:<br /><br /><tt>SELECT DISTINCT tag FROM logs ORDER BY
tag;</tt><br/><br /> But if you'd like to do a case insensitive order by like this:<br /><br /><tt>SELECT DISTINCT tag
FROMlogs ORDER BY UPPER(tag);</tt><br /><br /> or <br /><br /><tt>SELECT DISTINCT tag FROM logs ORDER BY
LOWER(tag);</tt><br/><br /> You get the following error message : <br /><big><tt><font>for SELECT DISTINCT, ORDER BY
expressionsmust appear in select list.</font></tt></big><br /><br /> It is weird as the order by column appears in the
select.<br /><br /> Any thoughts?<br /><div class="moz-signature"><br /> -- <br /> Aymeric Levaux<br /><a
href="http://www.javablackbelt.com"></a></div>

Aymeric Levaux <aymeric@javablackbelt.com> writes:
> SELECT DISTINCT tag FROM logs ORDER BY UPPER(tag);
> You get the following error message :
> for SELECT DISTINCT, ORDER BY expressions must appear in select list.

> It is weird as the order by column appears in the select.

No it doesn't.  The ORDER BY expression is "UPPER(tag)" not just "tag".
        regards, tom lane