Flying toasters and dense_rank

Have fun with this caps-lock user question :

asktom:Logic behind the DENSE_RANK

This is one more statement on how to not use order by

like in

select ename, deptno, row_number() over (order by 1)
from emp order by 2;
ENAME DEPTNO ROW_NUMBER()OVER(ORDERBY1)
---------- ---------- --------------------------
CLARK 10 1
KING 10 2
MILLER 10 3
JONES 20 4
FORD 20 5
ADAMS 20 6
SMITH 20 7
SCOTT 20 8
WARD 30 9
TURNER 30 10
ALLEN 30 11
JAMES 30 12
BLAKE 30 13
MARTIN 30 14


According to the doc, order by position is invalid. Actually, order by 1 is treated as order by ‘bananas’.
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

Thanks Tom for being so funny 🙂