Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id BED6B9FB508
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 15 Feb 2007 13:10:33 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 55631-02-2 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 15 Feb 2007 13:10:29 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 1A2D19FBC2F
	for <pgsql-sql@postgresql.org>; Thu, 15 Feb 2007 13:10:28 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l1FHAHlq027010;
	Thu, 15 Feb 2007 12:10:17 -0500 (EST)
To: Michael Glaesemann <grzm@seespotcode.net>
cc: Richard Huxton <dev@archonet.com>, Walter Cruz <walter.php@gmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: "for SELECT DISTINCT,
	ORDER BY expressions must appear in select list" - is that the
	standart or a limitation of postgresql?
In-reply-to: <57FD2B24-DB43-4EF7-92D1-3C467641AF9D@seespotcode.net> 
References: <32cabba0702150319v427d5a25s9a6c7f851576baa2@mail.gmail.com>
	<45D46196.2090003@archonet.com>
	<57FD2B24-DB43-4EF7-92D1-3C467641AF9D@seespotcode.net>
Comments: In-reply-to Michael Glaesemann <grzm@seespotcode.net>
	message dated "Thu, 15 Feb 2007 23:04:04 +0900"
Date: Thu, 15 Feb 2007 12:10:17 -0500
Message-ID: <27009.1171559417@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.049 tagged_above=0 required=5 tests=AWL, BAYES_50,
	SPF_HELO_PASS, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200702/170
X-Sequence-Number: 27504

Michael Glaesemann <grzm@seespotcode.net> writes:
> On Feb 15, 2007, at 22:35 , Richard Huxton wrote:
>> Walter Cruz wrote:
>>> The error: ERROR:  for SELECT DISTINCT, ORDER BY expressions must
>>> appear in select list is due to a standart implementarion or a design
>>> decision of postgres?
>> 
>> I think ORDER BY is defined to take place after DISTINCT, which  
>> means there is no meaningful "number" for it to order by. You could  
>> arbitrarily choose the first number encountered, but I can't see  
>> what sense it would make to order by them.

> I believe the reason is that DISTINCT depends on a sort to determine  
> uniqueness (distinctness), so it's a implementation detail that if  
> you're going to include an ORDER BY, you also need to include the  
> same columns in the ORDER BY in the DISTINCT clause.

No, there's actually a definitional reason for it.  Consider

	SELECT DISTINCT x FROM tab ORDER BY y;

For any particular x-value in the table there might be many different y
values.  Which one will you use to sort that x-value in the output?

Back in SQL92 they avoided this problem by specifying that ORDER BY
entries had to reference output columns.  SQL99 has some messy verbiage
that I think comes out at the same place as our restriction:

                   A) If K(i) is not equivalent to a <value expression>
                      immediately contained in any <derived column> in the
                      <select list> SL of <query specification> QS contained
                      in QE, then:

                      I) T shall not be a grouped table.

                     II) QS shall not specify the <set quantifier> DISTINCT
                        or directly contain one or more <set function
                        specification>s.


			regards, tom lane