Javatpoint Logo
Javatpoint Logo

SQL Server Row Number

Row number is the most common ranking function used in SQL Server. The ROW_NUMBER() function generates a sequential number for each row within a partition in the resultant output. In each partition, the first-row number begins with 1. We must always use the ORDER BY clause to ensure that the numbers are assigned in the proper sequence. The values returned by this function are of BIG INT data type. This function comes with SQL Server 2005 and later MS SQL versions.

The ROW_NUMBER determines the temporary value when the query is executed. If you want to get the numbers in a table, you need to see the IDENTITY property and SEQUENCE. When the ROW_NUMBER function in SQL Server encounters two identical values in the same partition, it assigns different rank numbers to them. The rank number will be determined by the order in which they are displayed.

There is no guarantee that the value returned by this function will be ordered the same for each execution unless the following are true:

  • Partitioned columns values are unique
  • ORDER BY columns values are unique
  • Combination of Partition and Order By columns are unique

Syntax

The following are the syntax that illustrates the ROW_NUMBER() function:

Let us understand the ROW NUMBER() function's syntax:

OVER

It's important to understand this clause that specifies the window or set of rows that the window function operates. The PARTITION BY and ORDER BY are the two possible clauses of the OVER clause. The OVER clause's ORDER BY expression is supported when the rows must come in a specific order for the function to execute.

PARTITION BY

  • It is an optional clause that splits the result set into partitions (groups of rows). Then ROW NUMBER() function is applied to each partition and assigns each partition's rank number separately.
  • If we omit the partition by clause, the ROW_NUMBER function will treat the whole result as a single partition and provide ranking in the top to bottom order.

ORDER BY

This clause allows us to sort the rows of the result set within each partition. It is a required clause because the ROW_NUMBER() function is order-dependent.

Example

Let us understand how the ROW_NUMBER function works in the SQL Server table with an example. First, we will create a table named "Persons" using the below statement:

Next, we will add some records into this table using the below statement:

Next, verify the data using the SELECT statement. We will get the below output:

SQL Server Row Number

1. Simple ROW_NUMBER() Example

The following statement displays the person's detail and adds a sequential integer number to each row by using the ROW_NUMBER():

Here, we have not specified the PARTITION BY clause so that the ROW_NUMBER() function will treat the whole result set as a single partition. After execution of the statement, we will get the following output:

SQL Server Row Number

2. ROW_NUMBER() over Partitions Example

The ROW NUMBER() function is used in this example provides a sequential number to each record within a partition in a table. It always reinitialized the row number when the year changes:

Here, we have used the PARTITION BY clause that divides the 'Persons' table into partitions based on the 'years' column. After execution, we will get the below output:

SQL Server Row Number

3. ROW_NUMBER() Example for Pagination

We can also use the ROW_NUMBER() function for pagination. For example, if we want to get all of a person's information in an application by pages, we'll first assign each row a sequential number using the ROW_NUMBER() function. Second, sort the rows by the requested page.

The following statement explains it more clearly:

It will give the following output:

SQL Server Row Number

If we do not want to use the subquery, we can use the common table expression (CTE) as below statement:

In this example, we have first specified the CTE that uses the ROW_NUMBER() function to assign each row in the result set sequentially. Second, the outer query returned the desired result. After execution, we will get the following output:

SQL Server Row Number

Conclusion

In this article, we learned how to give a sequential number to each record within a partition of a query using the ROW NUMBER() function in SQL Server.






Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA