Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id BD7609FBBBE
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 15 Feb 2007 19:00:13 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 87001-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 15 Feb 2007 19:00:01 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171])
	by postgresql.org (Postfix) with ESMTP id F1E889FB7ED
	for <pgsql-sql@postgresql.org>; Thu, 15 Feb 2007 19:00:01 -0400 (AST)
Received: by ug-out-1314.google.com with SMTP id k3so295961ugf
	for <pgsql-sql@postgresql.org>; Thu, 15 Feb 2007 14:59:59 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=ry15qb/PJSEKz3kgxsAcccCjtMZX/aHGzu0bccSatxtlrvI2cBv8ppgEMqdJB1Q6RHLbLrE/vFspWx7ObJXvcO6qNiU37pvDjVyCqMXdIGdH0oAOJAGphNho3lBkBk+164pfc5jgK8qotwO12GkRvoU/HClDC9/9NjbvieXR1BM=
Received: by 10.114.60.19 with SMTP id i19mr1540720waa.1171580398490;
	Thu, 15 Feb 2007 14:59:58 -0800 (PST)
Received: by 10.114.13.17 with HTTP; Thu, 15 Feb 2007 14:59:58 -0800 (PST)
Message-ID: <32cabba0702151459m4829c489y67b40f53e27134a1@mail.gmail.com>
Date: Thu, 15 Feb 2007 19:59:58 -0300
From: "Walter Cruz" <walter.php@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: "for SELECT DISTINCT,
	ORDER BY expressions must appear in select list" - is that the
	standart or a limitation of postgresql?
Cc: "Michael Glaesemann" <grzm@seespotcode.net>,
	"Richard Huxton" <dev@archonet.com>, pgsql-sql@postgresql.org
In-Reply-To: <27009.1171559417@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <32cabba0702150319v427d5a25s9a6c7f851576baa2@mail.gmail.com>
	<45D46196.2090003@archonet.com>
	<57FD2B24-DB43-4EF7-92D1-3C467641AF9D@seespotcode.net>
	<27009.1171559417@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=BAYES_50, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200702/172
X-Sequence-Number: 27506

Thanks Tom, Thank all :)

Maybe the commentary on parse_clase.c (beggining with "Now, DISTINCT
list consists of all non-resjunk") needs to be updated - In the
comment, looks likes this is a postgresql limitation.

[]'s
- Walter

On 2/15/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>