From aymeric@javablackbelt.com Sun Nov  4 16:49:19 2007
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 4578F9F9547
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  4 Nov 2007 12:49:19 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 73419-01-4 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  4 Nov 2007 12:49:14 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from finart.be (fanews.financialart.com [194.235.193.203])
	by postgresql.org (Postfix) with ESMTP id 937A19F94A2
	for <pgsql-sql@postgresql.org>; Sun,  4 Nov 2007 12:49:13 -0400 (AST)
Received: from [10.0.0.41] by finart.be
	(Cipher TLSv1:RC4-MD5:128) (MDaemon PRO v9.6.2)
	with ESMTP id md50002271935.msg
	for <pgsql-sql@postgresql.org>; Sun, 04 Nov 2007 17:48:29 +0100
Message-ID: <472DF7FE.1050504@javablackbelt.com>
Date: Sun, 04 Nov 2007 17:49:02 +0100
From: Aymeric Levaux <aymeric@javablackbelt.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: SELECT DISTINCT ... ORDER BY UPPER(column_name) fails
Content-Type: multipart/alternative;
	boundary="------------050903040309090201030505"
X-Authenticated-Sender: Aymeric@loop.be
X-HashCash: 1:20:071104:md50002271935::39HGhFwXWkF8iWfY:00002C2l
X-Spam-Processed: finart.be, Sun, 04 Nov 2007 17:48:29 +0100
	(not processed: message from valid local sender)
X-MDRemoteIP: 81.241.113.138
X-Return-Path: aymeric@javablackbelt.com
X-Envelope-From: aymeric@javablackbelt.com
X-MDaemon-Deliver-To: pgsql-sql@postgresql.org
Reply-To: aymeric@javablackbelt.com
X-MDAV-Processed: finart.be, Sun, 04 Nov 2007 17:48:30 +0100
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.611 tagged_above=0 required=5 tests=AWL=1.609,
	BAYES_50=0.001, HTML_MESSAGE=0.001
X-Spam-Level: *
X-Archive-Number: 200711/6
X-Sequence-Number: 29534

This is a multi-part message in MIME format.
--------------050903040309090201030505
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On pgsql 8.2, if you do the following query, everything is fine:

SELECT DISTINCT tag FROM logs ORDER BY tag;

But if you'd like to do a case insensitive order by like this:

SELECT DISTINCT tag FROM logs ORDER BY UPPER(tag);

or

SELECT DISTINCT tag FROM logs ORDER BY LOWER(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.

Any thoughts?

-- 
Aymeric Levaux
<http://www.javablackbelt.com>

--------------050903040309090201030505
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
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 FROM logs 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 expressions must 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>
</body>
</html>

--------------050903040309090201030505--




From tgl@sss.pgh.pa.us Sun Nov  4 17:12:46 2007
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 1ADE89F94B0
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  4 Nov 2007 13:12:46 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 11234-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sun,  4 Nov 2007 13:12:42 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 6F2429F94AC
	for <pgsql-sql@postgresql.org>; Sun,  4 Nov 2007 13:12:42 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lA4HCVYC015016;
	Sun, 4 Nov 2007 12:12:31 -0500 (EST)
To: aymeric@javablackbelt.com
cc: pgsql-sql@postgresql.org
Subject: Re: SELECT DISTINCT ... ORDER BY UPPER(column_name) fails 
In-reply-to: <472DF7FE.1050504@javablackbelt.com> 
References: <472DF7FE.1050504@javablackbelt.com>
Comments: In-reply-to Aymeric Levaux <aymeric@javablackbelt.com>
	message dated "Sun, 04 Nov 2007 17:49:02 +0100"
Date: Sun, 04 Nov 2007 12:12:31 -0500
Message-ID: <15015.1194196351@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/7
X-Sequence-Number: 29535

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