ROW_NUMBER function in SQL

Introduction

The ROW_NUMBER function helps you give a unique number to each row in your data, making it easy to order or pick specific rows.

When you want to number rows in a list, like ranking students by score.

When you need to pick the top N items from each group, like top 3 sales per region.

When you want to remove duplicate rows but keep one copy.

When you want to paginate results, showing rows page by page.

Syntax

SQL

ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY column3 [ASC|DESC], ...)

PARTITION BY divides the data into groups to restart numbering for each group.

ORDER BY decides the order in which rows are numbered.

Examples

Numbers all employees by salary from highest to lowest.

SQL

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank, employee_name, salary FROM employees;

Numbers employees within each department by their hire date.

SQL

SELECT department, employee_name, ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS hire_order FROM employees;

Gives a simple row number to all products ordered by their id.

SQL

SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, * FROM products;

Sample Program

This query ranks employees by their sales amount within each department, giving the highest sales person rank 1 in their department.

SQL

WITH sales_data AS (
  SELECT employee_id, department, sales_amount
  FROM sales
)
SELECT employee_id, department, sales_amount,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS sales_rank
FROM sales_data;

Important Notes

ROW_NUMBER always gives unique numbers, even if there are ties in ordering values.

If you want the same number for ties, consider using RANK() or DENSE_RANK() instead.

ROW_NUMBER requires an OVER clause with ORDER BY; without ORDER BY, numbering is not defined.

Summary

ROW_NUMBER assigns a unique number to each row in a result set.

Use PARTITION BY to restart numbering for groups.

ORDER BY controls the numbering order.