ROW_NUMBER Function in SQL Server

ROW_NUMBER() is one of the windows functions in the SQL server, used to the current number of rows in the result set based on an ORDER BY statement and sometimes based on an ORDER BY within a particular group. In SQL Server, the ROW_NUMBER() is a window function. In other words, it is also called an analytical function. This function is essential for various data breakdowns and interpretation. In this, it allocates a sequential number to each row within the section of an output set. The row number begins with 1 for the first row per section or partition. This window function mainly uses two SQL clauses. These are PARTITION BY and ORDER BY.

PARTITION BY: The PARTITION BY clause separates the outcome set into compartments. The ROW_NUMBER() function is used per section individually and restarts the row number for each section. The PARTITION BY clause is not mandatory though. If users decide to ignore it, the ROW_NUMBER() function will consider the entire output set as a unique and individual partition.

ORDER BY: The ORDER BY clause specifies the analytical sequence of the rows per partition of the result set. The ORDER BY clause is compulsory because the ROW_NUMBER() function is order liable.

This window function as mentioned earlier gives numbers to the output of the set. More precisely, it gives the index or order number of a row within a section of an output set, beginning at 1 for the first row per partition. Window functions are beneficial when users do not desire to tumble rows in the output, that is, aggregate the output data in one set of a row. It gives a single row for each row from the required condition. This function also allows users to execute computations against sections (i.e. subgroups or partitions) of an output set, normally a table or the consequences from a different query. The most significant advantage of window operations is that users can get the attribute of the rows from an accumulation. This Window function is one of the most important functions in SQL.

Its syntax is as follows:

ROW_NUMBER() OVER ( [PARTITION BY statements] ORDER BY statements )

Let’s consider a scenario wherein we have a query to get the name of products with quantity within an inventory for specific locations. We want to add a row number for each record returned. Its final query will be as below:

Here, we have generated row_number based on descending order of quantity.

Suppose we want to get row_number for each location in descending order of quantity, its query will be as below.

Here, as you can analyze the result, we are now having row_number partitioned by locationID.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.