SELECT DISTINCT i FROM dtest ORDER BY j - Mailing list pgsql-hackers

>From P121 "A Guide to the SQL Standard, C.J. Date, 1997",

S is defined as:

> SNO SNAME STATUS CITY
> --- ----- ------ ------
> 0S1 SMITH 000020 LONDON
> 0S2 JONES 000010 PARIS
> 0S3 BLAKE 000030 PARIS
> 0S4 CLARK 000020 LONDON
> 0S5 ADAMS 000030 ATHENS

and SP is defined as:

> SNO PNO QTY
> --- --- ---
> 0S1 0P1 300
> 0S1 0P2 200
> 0S1
> 0S2
> 0S2   etc...
> 0S3
> 0S4
> 0S5
The book says:

> 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 *** !!!
>

The book is rather unclear as to what "T" means... although
I assume that it means the relation T (SNO), and not the
table S (SNO,SNAME,STATUS,CITY).   Thus, if you want CITY 
to go in the order by, then you must include it in the 
select list.  

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.

Thus, it is a very useful, pratical short hand for:

SELECT SNO FROM (  SELECT S.SNO, S.CITY      FROM S  ORDER BY CITY )

I suggest that it might be useful to consider it in this 
manner...


So, assuming that this is a "good" interpretation of
the above item.  Let's consider how a DISTINCT fits in.
Clearly, it has no use in the inner query, where the
ordering occurs.

Thus, the distinct occurs on the outside, like:

SELECT DISTINCT i FROM (  SELECT i, j      FROM dtest  ORDER BY i )

Nice, hunh?  Well not exactly.  Consider this data:

i j
- -
B 3
A 1
A 5

Thus, after the inner query, we have the following:

i j
- -
A 1
B 3
A 5


Now we take the the DISTINCT i right?

What is the anwer?  The book talks about this in
the very next paragraph:

> Finally, if no ORDER BY clause is specified, the rows
> of T will hve an implementation-dependent ordering.  
> Likewise, if an ORDER BY clause is specified but does not
> define a "total" ordering, then the relative order of
> rows within T that have the same value for the order-item(s)
> will again be implemetnation-dependent.  For example:
>
> DELCARE W CURSOR FOR
>     SELECT SP.SNO, SP.PNO, SP.QTY
>       FROM SP
>   ORDER BY SNO
>
> Here the relevant order of SP rows with the same PNO value
> will be inplementation-dependent
>  

Since a "total" ordering was not provided in this 
case, and assuming that the "shorthand" above
is allowed, the answer is implementation-dependent.

i
-
A
B

or

i
-
B
A


Hope this helps. 

:) Clark