Row_number

ROW_NUMBER() is a SQL window function that assigns a unique number to each row in the result set.

Syntax:

ROW_NUMBER() OVER (
[ORDER BY column_name]
)

Features:

• Numbers are assigned based on the ORDER BY clause of ROW_NUMBER().
• In case of identical values in the ORDER BY clause, the function assigns numbers arbitrarily.
• In other words, the sequence of numbers generated by ROW_NUMBER() is not guaranteed to be the same for the same set of data.

Examples:

Example 1: Basic usage of ROW_NUMBER() on a single column

SELECT 
    name, 
    ROW_NUMBER() OVER (ORDER BY name) row_number
FROM 
    employees;

In this example, ROW_NUMBER() is used to assign a unique number to each row in the employees table, ordered by the employee names alphabetically.

Example 2: Using ROW_NUMBER() to rank rows in each partition

SELECT 
    department_id, 
    first_name, 
    salary, 
    ROW_NUMBER() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC) row_number
FROM 
    employees;

In this example, ROW_NUMBER() is used to rank employee salaries within each department (i.e., partitioned by department_id). In each department, employees with higher salaries are assigned lower row numbers.