[SQL] Mastering ROW_NUMBER() OVER(PARTITION BY ...) in Oracle SQL

In SQL, window functions are powerful tools for analyzing and processing data efficiently. One such function, ROW_NUMBER(), allows us to assign a unique sequential number to rows within a partition. In this blog post, we'll explore how ROW_NUMBER() works, its syntax, practical use cases, and related functions.

Understanding `ROW_NUMBER()`

The ROW_NUMBER() function is used to generate a unique row number for each row in a result set. When combined with the PARTITION BY clause, it resets the numbering for each partition (group of rows).

Syntax:

SELECT
    column_name,
    ROW_NUMBER() OVER(PARTITION BY partition_column ORDER BY order_column) AS row_num
FROM table_name;

Explanation:

ROW_NUMBER(): Generates a unique sequential number for each row.
OVER(): Defines the window function scope.
PARTITION BY partition_column: Divides the dataset into groups based on partition_column. The row number resets for each partition.
ORDER BY order_column: Determines the order of row numbers within each partition.

Example Use Case: Employee Salary Ranking

Sample Table: `employees`

Query to Assign Row Numbers Based on Salary in Each Department

SELECT
    emp_id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Output:

Here, the row number resets for each department, assigning unique numbers within each group.

Practical Use Cases of `ROW_NUMBER()`

1. Finding the Highest-Paid Employee in Each Department

SELECT * FROM (
    SELECT
        emp_id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
    FROM employees
) ranked
WHERE row_num = 1;

Output:

2. Implementing Pagination

For large datasets, pagination is essential. ROW_NUMBER() can help create paginated results:

SELECT * FROM (
    SELECT
        emp_id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER(ORDER BY emp_id) AS row_num
    FROM employees
) paged
WHERE row_num BETWEEN 1 AND 5;

This query fetches the first 5 rows from the dataset.

Related Window Functions

Besides ROW_NUMBER(), SQL provides other useful ranking functions:

1. `RANK()`

Assigns the same rank to duplicate values, skipping subsequent ranks.

SELECT
    emp_id,
    department,
    salary,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank_num
FROM employees;

2. `DENSE_RANK()`

Similar to RANK(), but does not skip ranks for duplicate values.

SELECT
    emp_id,
    department,
    salary,
    DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dense_rank_num
FROM employees;

3. `NTILE(n)`

Divides rows into n equal groups and assigns a group number.

SELECT
    emp_id,
    department,
    salary,
    NTILE(3) OVER(PARTITION BY department ORDER BY salary DESC) AS group_num
FROM employees;

When to Use `ROW_NUMBER()`

✅ Finding duplicate rows and removing them
✅ Selecting the nth highest or lowest value in a dataset
✅ Implementing pagination in SQL queries
✅ Assigning sequential numbers to rows for reporting

Conclusion

The ROW_NUMBER() OVER(PARTITION BY ...) function is a powerful SQL feature for ranking, filtering, and organizing data efficiently. By understanding this function and related ranking functions, you can significantly enhance your SQL querying skills.

The Cool Fox