? ROW_NUMBER() Function in SQL

The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set, ordered by specified columns. It’s very useful for ranking, pagination, or identifying row positions.

? Syntax:

ROW_NUMBER() OVER (    [PARTITION BY partition_column(s)]    ORDER BY sort_column(s)) AS row_num

• PARTITION BY: (Optional) Divides the result set into partitions to reset row numbering for each partition.

• ORDER BY: Specifies the order of the rows within each partition (or entire set if no partition).

? Example 1: Simple row numbering

SELECT    employee_id,    name,    ROW_NUMBER() OVER (ORDER BY employee_id) AS row_numFROM employees;

? Example 2: Row number per department

SELECT    employee_id,    name,    department_id,    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS row_numFROM employees;

? Sample Output for Example 2:

? Supported In:

• SQL Server

• Oracle

• PostgreSQL

• MySQL 8.0+

Use cases:

• Pagination (show N rows per page)

• Ranking rows

• De-duplicating records by ordering

Want an example with pagination using ROW_NUMBER()?