Re: Baffling SQL failure in Oracle - anyone explain?

Paul <paul_at_see.my.sig.com> wrote:
> In response to a post on comp.databases I did a bit of testing and
> found a very strange result.
>
> This works in Firebird (and something similar apparently works in
> MySQL)
>
> SELECT DISTINCT(First_Name)
> FROM Employee
> ORDER BY Last_Name

Since John can only show up once in the output, is it at the top of the sorted list (John Andrews), in the middle (John Meany), or at the end (John White)?

> There is a sample db Employee, somewhat similar to Scott.Emp.
>
> However these both fail in Oracle
>
> SELECT DISTINCT(Ename)
> FROM Emp
> ORDER BY Sal
>
> or even ORDER BY Emp.Sal
>
> with the error
>
> ERROR at line 3:
> ORA-01791: not a SELECTed expression
>
> Now, I can't for the life of me figure out why this query is failing
> in Oracle

Because it is a insane query?

> - I think that it's perfectly reasonable to sort on a column
> that one isn't selecting on.

I also think it is reasonable to sort on a column that isn't in the "select". Unless you are using distinct (or an aggregate function with no group-by) in the way that you are, in which case it is completely absurd to do so.

> This is so simple that I'm asking myself if I've overlooked something
> really *_really_* obvious.

You are missing the fact that it is not possible to fulfill the promise of both the distinct and the sort simultaneously.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB