RANK and DENSE_RANK on Oracle SQL

Topic Introduction:  In this tutorial, we will learn how to use Oracle RANK() and DENSE_RANK() functions to calculate the rank of rows within a set of rows.

RANK()

The RANK() function always returns sequential order for each record.

The RANK() function is an analytic function that calculates the rank of a value in a set of values.

The RANK() function returns the same rank for the rows with the same values. It adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

The RANK() function is useful for top-N and bottom-N queries.

DENSE_RANK() 

The DENSE_RANK() returns continuous order for each record.

The DENSE_RANK() is an analytic function that calculates the rank of a row in an ordered set of rows. The returned rank is an integer starting from 1.

Unlike the RANK() function, the DENSE_RANK() function returns rank values as consecutive integers. It does not skip rank in case of ties. Rows with the same values for the rank criteria will receive the same rank values.

SELECT EMPLOYEE_ID,

       LAST_NAME,

       DEPARTMENT_ID,

       SALARY,

       RANK () OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) "RANK",

       DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) "DENSE RANK"    

  FROM EMPLOYEES;