At 01:36 AM 2/7/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> At 12:26 AM 2/7/00 -0500, Tom Lane wrote:
>>> It would be interesting to poke at Oracle to find out just what they
>>> consider a legitimate ORDER BY expression for a SELECT DISTINCT.
>
>> I have full-time access to an Oracle installation, so fire away
>> regarding examples and questions.
>
>Well, try these on for size:
Here's what the Oracle proclaims:
select distinct x from foo order by x+1;
no rows selected
select distinct x+1 from foo order by x+1;
no rows selected
select distinct x+1 from foo order by x;
SQL> select distinct x+1 from foo order by x *
ERROR at line 1:
ORA-01791: not a SELECTed expression
select distinct x+1 from foo order by x+2;
SQL> select distinct x+1 from foo order by x+2 *
ERROR at line 1:
ORA-01791: not a SELECTed expression
select distinct x+y from foo order by x+y;
SQL>
no rows selected
I also tried: select distinct x+y from foo order by y+x,
which fails.
select distinct x,y from foo order by x+y;
SQL>
no rows selected
select distinct x+y from foo order by x,y;
SQL> select distinct x+y from foo order by x,y *
ERROR at line 1:
ORA-01791: not a SELECTed expression
select distinct x+y from foo order by x-y;
SQL> select distinct x+y from foo order by x-y *
ERROR at line 1:
ORA-01791: not a SELECTed expression
My first thought is that it is following a simple rule:
For arithmetic "order by" expressions, either:
1. The exact expression must also appear in the "select" list, and it must be exact, not just an expression that
computes the same value as the "order by" expressionor
2. all of the variables used by the expression must be listed in the "select" list as simple column names, not as
partof an expression.
Must be true.
At least, the rule is simple if you can compare expression trees.
At this point I still am clueless regarding the standard, I think I'll
make Date my morning coffee date again.
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.