SQL Server ROW_NUMBER() Function

ROW_NUMBER() is a window function used to assign a unique row number to each row in a result set. It is often used to sort the result set and return the ranking of each row in the result set.

Syntax

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC|DESC], ...
)

PARTITION BY: Optional. Divides the result set into multiple partitions based on the specified columns’ values.
ORDER BY: Required. Specifies the sorting column(s) and sorting order.

Use Cases

ROW_NUMBER() function is often used in the following scenarios:

Sorting the result set and labeling each row with row numbers.
Returning data for a specified page in a pagination query.
Filtering or selecting data in a query based on the row number.

Examples

Example 1

Assume there is a table called students that contains three columns: id, name, and score. You need to return the information of the students in descending order by score and assign a row number to each student. You can use the following SQL statement:

SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, id, name, score
FROM students

Executing the above SQL statement will produce the following result:

row_num	id	name	score
1	3	Mike	90
2	2	Tom	80
3	5	Jack	75
4	4	Lily	70
5	1	John	65

Example 2

Assume there is an orders table that contains four columns: order_id, order_date, customer_id, and amount. You need to return the earliest order information for each customer in January 2019 and assign a row number to each order. You can use the following SQL statement:

SELECT ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS row_num,
  order_id,
  order_date,
  customer_id,
  amount
FROM orders
WHERE YEAR(order_date) = 2019
  AND MONTH(order_date) = 1;