ROW_NUMBER Function in SQL

What is the ROW_NUMBER Function in SQL?

We saw a brief intro to the ROW NUMBER function in the previous section. This function is useful when we are doing paging using SQL Server. For every partition indicated in the OVER clause, the ROW NUMBER function provides successive numbering of the rows in the result by the order selected in the OVER clause. The first row gets the value one and then follows the increment of the number of succeeding rows.

SQL Server 2005, as well as later MS SQL versions, include the ROW NUMBER function.

Syntax of ROW_NUMBER Function in SQL

Let's look at the ROW_NUMBER() function's syntax.

Where, OVER - Arrange the rows in the desired order. ORDER BY - Sort the records in a certain order. PARTITION BY - The ROW NUMBER function in SQL has an optional clause named PARTITION BY. It's a clause that separates the output set into sections or groups of rows. The ROW NUMBER is then applied to each of these partitions, giving each one its rank number.

The ROW NUMBER function in SQL enumerates the rows in the over clause's sort order.

Function of ROW_NUMBER Function in SQL

ROW_NUMBER is an analytic function that is widely used in SQL. Let's go through some of the functions of this function:

• The function is responsible for assigning a unique identifier or number to every row to which it is implemented (either each row in the partition or each row returned by the query), starting with 1 in the order by clause.
• The ROW NUMBER function in the SQL Server is known as a SQL ranking function that gives every new record in a partition a sequential rank number.

Return Types of ROW_NUMBER Function in SQL

The return type of the ROW NUMBER function is bigint.

Examples of ROW_NUMBER Function in SQL

We'll use the example of an Employee table to demonstrate how the ROW NUMBER function works.

The ROW_NUMBER() function is used in the given statement to assign a number value to every employee in the table given above.

Output:

How to Use ROW_NUMBER in SQL Query?

The ROW NUMBER() function is used in the following statement to allocate a sequential number to each user row:

Output:

We missed the PARTITION BY clause in this example, so the ROW NUMBER() function handled the entire result set as a single partition.

How to use SQL ROW_NUMBER Function with PARTITION clause

The clause PARTITION BY divides the output set into partitions (which, in layman's terms, signifies a group of rows). The ROW NUMBER function is applied to every partition independently, and the row number for each of these partitions is reinitialized.

The phrase PARTITION BY is optional. If you skip, the ROW NUMBER method will treat the entire result set as a single partition.

Let's go through an example to understand the concept in a better manner. We will make use of the partition clause to split the employees into groups based on their nationality.

Output:

As we can notice from the above output, the employees have been divided based on their country. Each time the country's name changes, the sequentially allocated integer, or ROW NUMBER, is reinitialized. Finally, all the partitions are sorted according to the country name in the final output.

How to Return a Subset of Rows Using CTE and ROW_NUMBER?

The row number function in SQL can also be used for pagination. For example, programmers can display a client list by page, with a total of ten rows per page.

EMPLOYEE TABLE:

CTE is defined as a named temporary result set that can be used in the processing scope of SELECT, UPDATE, DELETE, INSERT, and MERGE statements. The following code line demonstrates how to utilize the ROW NUMBER method and CTE to return employee rows 1 through 5.

Output: