RANK,ROW_NUMBER and DENSE_RANK

RANK(),ROW_NUMBER() and DENSE_RANK()

ROW_NUMBER():

This function assigns a unique sequential integer to each row within a partition of a result set, starting at one for the first row in each partition. It does not account for ties; every row gets a unique number.

RANK():

This function assigns a rank to each row within a partition of a result set, with the same rank given to rows with equal values. However, it skips ranks for tied rows. For example, if two rows share a rank of 1, the next rank assigned will be 3.

DENSE_RANK():

Similar to RANK(), this function assigns the same rank to rows with equal values but does not skip any ranks. If two rows are ranked 1, the next rank will be 2.

Sample Data Creation in Oracle
First, let’s create the employees table and insert the sample data:

Create the employees table

-- Create the employees table
CREATE TABLE employees (
    employee_id NUMBER,
    department_id NUMBER,
    salary NUMBER
);

Insert the sample data

-- Insert sample data
INSERT INTO employees (employee_id, department_id, salary) 
VALUES (1, 10, 60000);
INSERT INTO employees (employee_id, department_id, salary) 
VALUES (2, 10, 70000);
INSERT INTO employees (employee_id, department_id, salary) 
VALUES (3, 10, 70000); -- Tie with employee 2
INSERT INTO employees (employee_id, department_id, salary) 
VALUES (4, 20, 80000);
INSERT INTO employees (employee_id, department_id, salary) 
VALUES (5, 20, 75000);

Query Using All Three Ranking Functions
Now, write a query that uses

ROW_NUMBER(), RANK(), and DENSE_RANK()
to see how each function behaves:

SELECT 
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() 
OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK() 
OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
    DENSE_RANK() 
OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM employees;

Expected Output
When you run the above query, you would get the following results:

employee_id department_id   salary  row_num rank    dense_rank
2   10  70000   1   1   1
3   10  70000   2   1   2
1   10  60000   3   3   3
4   20  80000   1   1   1
5   20  75000   2   2   2
Explanation of Each Ranking Function Output

ROW_NUMBER():

Output: Assigns a unique sequential integer to each row within the partition.

Result:
Employee 2 gets 1.
Employee 3 gets 2 (distinct number despite being tied with Employee 2).
Employee 1 gets 3.

RANK():

Output: Assigns the same rank to rows with equal values and skips the next rank for subsequent distinct values.

Result:
Employees 2 and 3 both get 1 (same salary).
Employee 1 gets 3, skipping 2

DENSE_RANK():

Output: Similar to RANK(), but does not leave gaps in the ranking sequence.

Result:
Employees 2 and 3 both get 1 (same salary).
Employee 1 gets 2 (the next rank).

Summary of Differences

ROW_NUMBER(): Provides a unique number for each row, even for ties.
RANK(): Assigns the same rank to tied rows but skips numbers for subsequent distinct ranks.
DENSE_RANK(): Assigns the same rank to tied rows but does not skip any ranks.
This example clearly illustrates how ROW_NUMBER(), RANK(), and DENSE_RANK() work in Oracle SQL and the differences in their outputs, especially in cases of ties.