Rank Function in Oracle SQL

As an Aggregate function, the RANK function returns the rank of a row within a group of rows.

The syntax for the RANK function when used as an Aggregate function is:

RANK( expression1, … expression_n ) WITHIN GROUP ( ORDER BY expression1, … expression_n )

PARAMETERS OR ARGUMENTS

expression1 .. expression_n can be one or more expressions which identify a unique row in the group.

Note: There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.

The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.
Example Query:
select RANK(10000, 200) WITHIN GROUP (ORDER BY sal,comm) from emp;
The SQL statement above would return the rank of an employee with a salary of $10,000 and a commision of $200 from within the employees table.

Rank Function Syntax #2 – Used As An Analytic Function:

Here RANK function returns the rank of each row of a query with respective to the other rows.

rank() OVER ( [ query_partition_clause] ORDER BY clause )

Example Query:
select ename, deptno,sal, RANK() OVER (PARTITION BY deptno ORDER BY sal) from emp

The SQL statement above would return all employees and calculate a rank for each unique salary in all department. If two employees had the same salary, the RANK function would return the same rank for both employees like shown in below query results. However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is different from the DENSE_RANK function which generates consecutive rankings.

You can watch video tutorial of Rank() Aggregate function here:

You can watch video tutorial of Rank() Analytic function here: