The ROW_NUMBER function is a built-in ranking function in SQL Server that assigns a sequential number to each row in a result set. This ranking function is useful for assigning a unique identifier to each row in a result set, which can then be used for ordering or filtering data. The ROW_NUMBER function is a built-in function in SQL Server that assigns a sequential number to each row in a result set.

Syntax

ROW_NUMBER() OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)

The ROW_NUMBER function is a part of the ranking functions available in SQL Server. Ranking functions calculate a value for each row in a result set based on the values of other rows in the result set. The ROW_NUMBER function assigns a sequential number to each row in a result set, starting with 1 for the first row.

ROW_NUMBER can be used to generate a list of sequential numbers, which can be useful for ordering data or creating unique identifiers.

Example

Here is an example of how the ROW_NUMBER function can be used to order data:

SELECT
ROW_NUMBER() OVER(ORDER BY price, name) AS RowNum,
price, name
FROM ebooks;

This query will return the price and name of all ebooks, along with a sequential number that has been generated for each row. The rows have been ordered by price and name.

ROW_NUMBER with TOP

The ROW_NUMBER function can be used in conjunction with other SQL Server functions to perform more complex tasks. For example, the following query uses the ROW_NUMBER function to return the top 5 ebooks by price:

SELECT TOP 5
ROW_NUMBER() OVER(ORDER BY price DESC) AS RowNum,
price, name
FROM ebooks;

ROW_NUMBER with PARTITION BY clause

The following example shows how the ROW_NUMBER function can be used with the PARTITION BY clause to number rows within a partition:

SELECT name, price,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY price DESC) AS RowNum
FROM ebooks;