AntDB-Oracle Compatibility Developer’s Manual P3–55

RANK

Determines the ranking of a set of values. When there are the same sorted values, there will be the same ranking and the number of rows with the same values will be recorded in the next ranking.

Example:

Calculate the salary ranking of each person within the same department.

select empno,ename,sal, deptno, rank() over (partition by deptno order by sal)  from emp;
 EMPNO | ENAME  |  SAL  | DEPTNO | RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL)  
-------+--------+-------+--------+---------------------------------------------------
  7934 | MILLER |  6300 |     10 |                                              1
  7782 | CLARK  |  7450 |     10 |                                              2
  7839 | KING   | 10000 |     10 |                                              3
  7469 | SMITH  |  4800 |     20 |                                              1
  7369 | SMITH  |  6800 |     20 |                                              2
  7566 | JONES  |  7975 |     20 |                                              3
  7902 | FORD   |  8000 |     20 |                                              4
  7788 | SCOTT  |  8000 |     20 |                                              4
  7876 | ADAMS  |  8100 |     20 |                                              6
  7900 | JAMES  |  5950 |     30 |                                              1
  7521 | WARD   |  6250 |     30 |                                              2
  7654 | MARTIN |  6250 |     30 |                                              2
  7844 | TURNER |  6500 |     30 |                                              4
  7499 | ALLEN  |  7600 |     30 |                                              5
  7698 | BLAKE  | 11850 |     30 |                                              6
(15 rows)

DENSE_RANK

The DENSE_RANK window function determines the ranking of one of a set of values based on the ORDER BY expression in the OVER clause. If the optional PARTITION BY clause is present, the ranking is reset for each group of rows. Rows with the same values that meet the ranking criteria receive the same ranking. The DENSE_RANK function differs from RANK in that there is no spacing in the sequence of sorted values if two or more rows are tied. For example, if two rows have a ranking of 1, the next ranking is 2.

Example:

postgres=# SELECT deptno, ename, sal, DENSE_RANK ( ) OVER ( partition BY deptno ORDER BY sal DESC ) "RANK" FROM emp WHERE sal>6500;
 DEPTNO |  ENAME |  SAL  | RANK 
--------+--------+-------+---------
     10 | KING   | 10000 |    1
     10 | CLARK  |  7450 |    2
     20 | ADAMS  |  8100 |    1
     20 | SCOTT  |  8000 |    2
     20 | FORD   |  8000 |    2
     20 | JONES  |  7975 |    3
     20 | SMITH  |  6800 |    4
     30 | BLAKE  | 11850 |    1
     30 | ALLEN  |  7600 |    2
(9 rows)