Re: [HACKERS] SELECT DISTINCT i FROM dtest ORDER BY j

Clark Evans <clark(dot)evans(at)manhattanproject(dot)com> writes:
>> From P121 "A Guide to the SQL Standard, C.J. Date, 1997":
>> Note that each order-item must identify a column
>> of T itself, not just a column of some table from
>> which T is derived. Thus, for example, the following
>> is ***ILLEGAL***:
>>
>> DELCARE Z CURSOR FOR
>> SELECT S.SNO
>> FROM S
>> ORDER BY CITY
>> -- *** ILLEGAL *** !!!

How interesting. I believe that in fact Postgres used to refuse
such queries, and that the ability to order by a field not present
in the result is new in 6.4. Maybe now we are starting to find out
why the SQL spec forbids it ;-)

> But wait! Oracle allows the above query! From what I
> understand though, the database engine implicitly includes
> the CITY in the internal processing, the information
> is merely discarded after the order by and not returned.

Right, that's how Postgres does it too.

Meanwhile darrenk wrote:
> It would seem to me that the distinct should apply first though.
> I would expect the ORDER BY clause to order whatever tuples are
> returned by the SELECT, and that would imply doing DISTINCT first.

The trouble is that if you have several tuples with the same i and
different j, doing the DISTINCT first implies throwing away all but
one of those tuples. Which one do you keep? It matters because
some of those tuples might sort differently than others.

As far as I can see, this combination of features is not well-defined on
its surface. You have to make some additional assumptions (about which
of the possible j values is kept for sorting) in order to define a
unique result.

regards, tom lane