DENSE_RANK in Oracle/PL-SQL

DENSE_RANK Function:

• Returns the rank of a value in a group of values.
• A built in analytic function which is used to rank a record within a group of rows. 
• Return type is number and serves for both aggregate and analytic purpose in SQL.
• Rows with equal values for the ranking criteria receive the same rank.
• The ranks are consecutive. No ranks are skipped if there are ranks with multiple items.
  
  Examples:
  1.  Query to return the dense_rank for a $50000 salary(Single Column DENSE_RANK)

  
  SELECT DENSE_RANK(50000) WITHIN GROUP
  (ORDER BY salary DESC NULLS LAST) SAL_RANK
  FROM employees;
  

  
  

  2.  Query to return the dense_rank for an employee with a salary of $50,000 

  and a commission of 10%$(Multiple Column DENSE_RANK)

  
  SELECT DENSE_RANK(10,50000) WITHIN GROUP
  (ORDER BY commission_pct, salary) SAL_RANK
  FROM employees;

  
  

  3. Query to rank the employees in department '60' based on their salaries. Identical salary values receive the same rank. However, no rank values are skipped. 

  SELECT department_id, last_name, salary,

         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) DENSE_RANK

    FROM employees 

    WHERE department_id = 60

    ORDER BY DENSE_RANK, last_name;

  
  

  
  

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical