DENSE_RANK ORDER BY NULL always return 1

Question

I find in PROD procedure code like:

select *
from (select "EMPNO",
    "SAL",
    "COMM",
    "DEPTNO",
    DENSE_RANK() OVER (PARTITION BY deptno ORDER BY null) AS drank
    from SCOTT."EMP"
    where deptno in (10,30))
where drank = 1
order by deptno

RESULT:

EMPNO   SAL      COMM  DEPTNO   DRANK
7934    1300     -     10       1
7839    5000     -     10       1
7782    2450     -     10       1
7844    1500     0     30       1
7900    950      -     30       1
7654    1250     1400  30       1
7499    1600     300   30       1
7698    2850     -     30       1
7521    1250     500   30       1

As result drank is always equal to 1. This is also true for:

DENSE_RANK() OVER (ORDER BY null) AS drank

DENSE_RANK() OVER (PARTITION BY comm ORDER BY null) AS drank

DENSE_RANK() OVER (PARTITION BY 1 ORDER BY null) AS drank

DENSE_RANK() OVER (PARTITION BY null ORDER BY null) AS drank

Is there any case when drank is not equal to 1 when there is ORDER BY null clause?

EDIT: I know dense_rank start with 1. Question is about values greater than 1.

Answer 1

The documentation says:

Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.

Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.

Whenever the order_by_clause results in identical values for multiple rows, the function behaves as follows:

• CUME_DIST, DENSE_RANK, NTILE, PERCENT_RANK, and RANK return the same result for each of the rows.
• ...

When you order by null, the order_by_clause results in identical values for multiple all rows (in the partition), so they all get the same result.

The documentation for dense_rank also says:

The ranks are consecutive integers beginning with 1.

So they get the same result, which has to be 1.