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. 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!-

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>