Re: Planning aggregates which require sorted or distinct

Hi,

Tom Lane wrote:
>> select empno, rank() over (order by salary) as srank,
>> rank() over (order by age) as arank
>> from employees order by empno;
>
> Eeek. This seems like the worst sort of action-at-a-distance. How does
> rank() know what value it's supposed to report the rank of?

All of these ranking aggregate functions (rank, dense_rank,
percent_rank, cume_dist and row_number) are normally used without any
arguments, see examples in [1] or [2]. However, they explicitly require
an ORDER BY clause anyway, so I suppose they need one with exactly *one*
argument? Does the standard say anything more explicit? Or should those
functions just take the first ORDER BY argument?

I.e. what should the following query do? Is it a legal query at all?

select empno, cume_dist() over (order by salary, age) as rank,
from employees order by empno;

Regards

Markus

[1]: SQL Anywhere Server - SQL Reference, Window Clause:
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbrfen10/rf-window-clause-statement.html

[2]: A techonthenet.com article about cume_dist() function:
http://www.techonthenet.com/oracle/functions/cume_dist.php