RANK,ROW_NUMBER and DENSE_RANK

Sure! include RANK() along with ROW_NUMBER() and DENSE_RANK() in an example. This will help illustrate the differences among the three ranking functions using a sample dataset in Oracle.

Sample Data Creation in Oracle

First, let’s create the employees table and insert the sample data:

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

-- 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.