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