SELECT DISTINCT i FROM dtest ORDER BY j

>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