Re: SQL 2005 Ambiguous column name

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
.

Relevant Pages

  • Re: NOT IN More Efficient???
    ... Internally in and exists clauses are evaluated differently.EXISTS clause ... --create sample data table with one row that has customerid null. ... If any of the rows from the subquery returns null value and if you are using ... You can use NOT EXISTS clause or LEFT OUTER JOIN to get the required result ...
    (microsoft.public.sqlserver.mseq)
  • Re: SQL Update query
    ... "Vishal Parkar" wrote: ... Whereas EXISTS clause checks ... > where customerid not in(select customerid from orders union all select null) ...
    (microsoft.public.sqlserver.mseq)
  • Re: SQL Update query
    ... Internally in and exists clauses are evaluated differently.In clause is ... where customerid not in(select customerid from orders union all select null) ... If any of the rows from the subquery returns null value and if you are using ...
    (microsoft.public.sqlserver.mseq)
  • Re: Syntax error (missing operator) in query expression
    ... then save that SQL as a passthrough and enjoy ... INNER JOIN ... requires brackets when the ON clause doesn't immediately follow the JOIN ... customerID]. ...
    (microsoft.public.access.queries)
  • Re: Group By?!
    ... If I were to take out that huge group by clause, ... Let's try and demonstrate the principle with a simpler query. ... Now lets group by CustomerID and EmployeeID, ...
    (microsoft.public.access.devtoolkits)