Posted by Serge Rielau on 10/03/07 13:58

steve wrote:
> But why does this work?
>
> SELECT CustomerID,CustomerID
> FROM Orders
>
> SELECT DISTINCT CustomerID,CustomerID
> FROM Orders
>
> This is what I was talking about in:
>
> http://beyondsql.blogspot.com/2007/10/sql-two-standards-of-sql-cte.html
>
These are two completely separate topics.
Let's apply real life as a metaphor here:
There is no rule against (in fact it is quite likely that) you have two
kids with the same first name in class (Say: "William").
A problem only arises when you try to reference them (e.g. in the ORDER
BY clause or an outer query).
Then you better give them distinct names "Bill", "Billy", "Will", ...

The intent of a CTE is that it is referenced. The same isn't necessarily
true for columns in the select list (positional bind-out of a cursor)

ORDER BY is even more interesting here since it has to resolve against
two scopes: The "exposed" column names in the select list as well as the
columns accessible through the FROM clause. AFAIK the closer scope for
ORDER BY is the select list. So it's within the perogative of the DBMS
DBMS to raise an error here.
Things get more clearer when we enhance the example a bit:
SELECT customerid AS X, customerid * -1 AS X FROM orders ORDER BY X

The DISTINCT keyword (and GROUP BY while we're at it) has absolutely
NOTHING to do with ORDER BY.
There are plenty of other ways to de-dup a set than ORDERering. E.g.
hashing, or reliance on partitioning of various flavours.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Back to original message]