Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?

["Walter Cruz" <walter(dot)php(at)gmail(dot)com>]

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Michael Glaesemann <grzm(at)seespotcode(dot)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