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 🙂

2 thoughts on “Flying toasters and dense_rank

  1. joel garry

    Oh man, it was all I could do to not spout the old joke:

    Excuse me, you have a banana in your ear.

    What?

    I said, EXCUSE ME, YOU HAVE A BANANA IN YOUR EAR!

    I can’t hear you, I have a banana in my ear!

    WATCH OUT FOR THE FLYING TOASTER!

    What?

    -WHAM!-

Comments are closed.