Today, I’ll show you how some of the RANK functions use window functions.

RANK() OVER ( query_partition_clause ORDER_BY clause)
DENSE_RANK( ) OVER ( query_partition_clause ORDER_BY clause )

Example

Partition scott.emp as deptno and select sal from scott.emp as deptno.

So let’s look at the RANK situation.

SQL> conn scott/triger; Get the promise されました. SQL> col ENAME for a30 SQL> set lin 120 pages 999 SQL> SELECT deptno, ename, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rank FROM emp; 2 3 DEPTNO ENAME SAL RANK ---------- ------------------------------ ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2  10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 1 20 JONES 2975 3 20 ADAMS 1100 4 20 SMITH 800 5 30 BLAKE 2850 1 30 ALLEN 1600 2 30 Turner 1500 3 30 Martin 1250 4 30 WARD 1250 4 30 James 950 6 14 lines が Sentaku

Now let’s look at DENSE_RANK.

SQL> SELECT deptno, ename, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rank FROM emp; 2 3 DEPTNO ENAME SAL RANK ---------- ------------------------------ ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2  10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 1 20 JONES 2975 2 20 ADAMS 1100 3 20 SMITH 800 4 30 BLAKE 2850 1 30 ALLEN 1600 2 30 Turner 1500 3 30 Martin 1250 4 30 WARD 1250 4 30 James 950 5 14 lines が Sentaku

Let’s see what the difference is.

RANK:  DEPTNO ENAME SAL RANK ---------- ------------------------------ ---------- ---------- 20 SCOTT 3000 1 20 FORD 3000 1 <<<1 20 Jones 2975 3 <<<3 20 Adams 1100 4 20 Smith 800 5 Dense_rank:  DEPTNO ENAME SAL RANK ---------- ------------------------------ ---------- ---------- 20 SCOTT 3000 1 20 FORD 3000 1 <<<1 20 JONES 2975 2 <<<2 20 ADAMS 1100 3 20 SMITH 800 4

We can see that if RANK is the same RANK, the next RANK will be available, while DENSE_RANK will not be available.

So how do you rank by the number of records? You can use ROW_NUMBER as the profiling function.

SQL> SELECT deptno, ename, sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) as rank FROM emp; 2 3 DEPTNO ENAME SAL RANK ---------- ------------------------------ ---------- ---------- 10 KING 5000 1 10 CLARK 2450 2  10 MILLER 1300 3 20 SCOTT 3000 1 20 FORD 3000 2 20 JONES 2975 3 20 ADAMS 1100 4 20 SMITH 800 5 30 BLAKE 2850 1 30 ALLEN 1600 2 30 Turner 1500 3 30 Martin 1250 4 30 WARD 1250 5 30 James 950 6 14 lines が Sentaku

2021/04/16 @ Dalian