SQL Server Row_Number – Complete tutorial

In this sql server tutorial, we will learn about the SQL Server ROW_NUMBER function, Different examples related to SQL Server Row_Number, and will cover the following topics.

• SQL Server Row_Number
• SQL Server Row_Number partition
• SQL Server Row_Number over
• SQL Server Row_Number without over
• SQL Server Row_Number without order by
• SQL Server Row_Number dense_rank
• SQL Server Row_Number in where clause
• SQL Server Row_Number starting value
• SQL Server Row_Number group by

For all the examples, I have used sql server 2019 express and sql server management studio.

SQL Server Row_Number

The Row_Number in SQL Server is a function that generates a sequential integer to each row within a result set’s partition. And for the first row in each partition, the row number begins with 1. This function returns temporary values that are calculated when the query is executed.

The Row_Number function in SQL Server has the following syntax.

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] 
            order_by_clause )

• The PARTITION BY clause in the above syntax is used to divide the result set into groups of rows or partitions. And the column by which the result set is partitioned is specified by value_expression.
• The Row_Number() function is applied to each partition independently, and the row number for each partition is reinitialized.
• The PARTITION BY is an optional argument, if we don’t use the PARTITION BY clause, the Row_Number() function will treat the entire result set as a single partition.
• The ORDER BY clause in the above syntax is used to specifies the logical order in which the rows of the result set are organized within each partition.
• Because the ROW NUMBER() function is order sensitive, the ORDER BY clause is mandatory to use.

Note- There’s no guarantee that the rows returned by a SQL query that uses the SQL ROW NUMBER function will always be in the same order.

Example

Now, for example, consider the following sample table given below, which does not have any numeric order. And we will use the Row_Number() function to temporarily add the row number in the table.

For this implementation, we are going to execute the following SQL query.

SELECT 
SELECT 
   ROW_NUMBER() OVER (
	ORDER BY [Name]
   ) Sr_no,
   [Name],
   [Gender],
   [City],
   [Contact]
FROM 
   SampleTable;

In the above example, we are using the Name column in the ORDER BY clause, And because we didn’t use the PARTITION BY clause, ROW NUMBER() interpreted the entire result set as a single partition. In the end, we get the following result as an output.

Read: IDENTITY_INSERT in SQL Server

SQL Server Row_Number partition

• The Row_Number() function in SQL Server 2019 carries an optional parameter named PARTITION BY. The PARTITION BY clause is used to divide the result set into groups or partitions. Next, ranking functions are applied to each record partition individually, and the rank for each record partition is reset to 1.
• If the PARTITION BY clause is ignored, all of the entries in the result set will be treated as part of a single record group or partition, and ranking functions will be performed.

Example

SELECT *, ROW_NUMBER() OVER(Partition by City ORDER BY City) AS Row_Number 
from SampleTable  

• In the above example, we are using the Row_Number() function within the SELECT statement.
• And in Row_Number() function, we are using the Partition By and Order By clause on the City column.
• So the function will first order the table based upon City names, and it will assign the numeric value to a record based upon the values in the City column. And it will return the following result.

SQL Server Row_Number Over

The Over clause is an important part of the Row_Number() function. It is used to determine how a rowset should be partitioned and ordered before any associated window function is applied to the data.

Syntax

OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]  
      )  

The OVER clause has mainly two clauses: PARTITION BY and ORDER BY. The PARTITION BY clause is used to divide or partition the data and the ORDER BY is used to arrange data in a specific order.

Now to execute the Row_number function on a table, it is mandatory to use the Over clause as, without it, the SQL Server will return an error.

Example

SELECT *, ROW_NUMBER() OVER(Partition by Dept ORDER BY Dept) AS Row_Number 
from Employee 

In the above example first, we are selecting all the Columns of the Employee table using the SELECT statement. After this, we are using Row_number() function, and with this, we are using the OVER clause to arrange and partition the data according to the values in the Dept column.

After successful query execution, we will get the following output.

SQL Server Row_Number without over

The Row_Number() function in SQL Server 2019/2017 is used to generate a serial number for a given recordset. However, we must always include the ORDER BY clause with the Row_Number function to ensure that the numbers are assigned to the correct order.

Now it is always mandatory to use the OVER clause with Order By argument, and if we don’t include the OVER clause with Row_Number, the SQL Server will return an error.

SELECT *, ROW_NUMBER() AS Row_Number 
from Employee 

In the above code, we have not included the OVER clause with Row_number() function, and now if we try to execute this code, it will return the following error.

SQL Server Row_Number without order by

We define the ORDER BY clause with Row_Number() function to ensure that the numbers are assigned to the correct order. But what if we want the row numbers to be generated in the same order as the data is entered?

Is it possible to skip the ORDER BY clause?

The answer to this question is NO, we cannot skip the ORDER BY clause. The SQL Server will raise an error if we try to ignore the ORDER BY clause.

Still, there is a solution if we want the row numbers to be generated in the same order as the data is entered. For this implementation, we can use any literal value instead of using the column name in the ORDER BY clause.

SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT 100)) AS Row_Number 
from Employee  

In the above query, we are using the “SELECT 100” statement instead of defining any column name with Order By clause. Now the query will add the Row_Number column without ordering any table value.

SQL Server Row_Number starting value

The ROW_NUMBER() is a window function in SQL Server that assigns a sequential integer to each record within the partition of a result set. And the integer value always starts with one (1) for every partition.

Example

SELECT ROW_NUMBER() OVER(ORDER BY Dept) AS 'Sr_No', *
FROM Employee;

In the above example, we are ordering the records of the Employee table based upon Department names available in the Dept column. And then, the Row_Number() function will assign an integer value to each record in the table starting from one (1). As in the example, we have not used Partition by clause, so the Row_Number will consider the entire result set as a single partition.

But what if we want to start with some other integer value using Row_Number() function like 101, 901, etc. For this implementation, we can follow the following syntax given below.

SELECT number - 1 + ROW_NUMBER() OVER(partition_by_clause, order_by_clause)

The number in the above syntax is used to represent the integer value from where we want to start the Row_Number() function. And we adding the row number value for each record to (number – 1). So by using a basic mathematical operation (number – 1 + 1), the Row_Number() function will start from the number we specify.

Now for demosntration, consider the following example give below.

SELECT 301 - 1 + ROW_NUMBER() OVER(order by Dept) as id, *
FROM Employee

In the above code, we have specified the number as 301, so the Row_Number() function will start from 301 for the whole result set.

Read: SQL Server drop table if exists

SQL Server Row_Number in where clause

Now the most easiest and efficient way to use the result of the Row_Number() function in the WHERE clause is by using the CTE.

The common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE query. It was introduced in SQL Server 2005. And we can also utilize a CTE as part of the SELECT query in a view.

Syntax

WITH expression_name [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )

The expression_name is followed by a “WITH” in the CTE query. We’ll write our CTE query definition and use this expression name in our select query to present the outcome of our CTE Query.

Select [column_1,column_2,..] from expression_name

Now for demonstration of using the result of Row_Number() function in WHERE clause, consider the following example given below.

WITH MyTable AS 
(
  SELECT ROW_NUMBER() OVER(Order By Name) AS id, [Name], [Dept]
  FROM Employee 
)

SELECT *
 FROM MyTable
 WHERE id > 4

• In the example, we are defining a CTE expression named “MyTable”.
• And in the expression, we have defined the Row_Number() function within the SELECT statement, and also we are selecting the Name and Dept column.
• After this, we are using the SELECT statement to project the result of the MyTable expression and then we are using the alias name of Row_Number() function in the WHERE clause to filter out the result.

After successful query execution, we will get the following result.

SQL Server Row_Number group by

In Row_Number() function, we use the Partition By clause to partition the data in the given table. And based upon that partition, Row_Number() function will assign the integer values to each record starting from 1.

And on the other side, the Group By statement in SQL Server is used to group rows that have the same values. And there are very less situations where we have to use Row_Number() function and Group By statement together.

Here, we will illustrate a simple example to find unique city and county names from the table. Also, instead of using Partition By clause with Row_number(), we will use the GROUP BY clause.

Here is the sample data of the table that we are going to use in the illustration. And the name of the table is tblCustomers.

In the above table, we will easily note that there are multiple records from the same country and even the same city. For example, New York, Vancouver, etc. Now, we will use the Row_number and GROUP BY state to fetch only unique city and country names.

USE [sqlserverguides]
GO

SELECT ROW_NUMBER() OVER(ORDER BY city) AS 'Sr_No', city, country 
FROM tblCustomers GROUP BY city, country 

In the above code, first, we are using the Row_Number() function to order the City column. And we are fetching the values of city and country column. After this, we are using the GROUP BY statement to find unique city and country names available in the table. In the end, the query will return the following result.

SQL Server Row_Number vs Dense_Rank

The Dense_Rank() in SQL Server, is a window function that assigns a rank to each row within a partition of a result set. The DENSE RANK() function returns a list of rank values in order. And if the values in each partition’s rows are the same, they acquire the same rank.

The DENSE RANK() function has the following syntax:

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

• Similar to Row_Number(), the Dense_Rank() function also carries two arguments.
• The first is Partition By clause used to partition the data
• And the second is Order By clause used to define the order in which data should appear.
• It is not necessary to use the Partition By clause. If this parameter is left blank, the function will treat the entire result set as a single partition.

Example

SELECT *, DENSE_RANK() OVER(ORDER BY Dept) AS 'dense_rank' 
FROM Employee

In the above example, we are using the Dense_Rank() function to assign a rank to each record in the table based upon ordered values in the Dept column. In the end, we will get the following output.

The main difference between Row_Number() and Dense_Rank() functions are as follows.

• Even when there are duplicate values, the Row_Number() function will always produce a unique integer value for each record.
• Whereas DENSE RANK() function assigns the same number to each record with the same value, rather than skipping to the next number.

You may like the following SQL Server tutorials:

• SQL Server Row_Number Join
• How to insert a row in a view in SQL Server
• Delete Duplicate Rows in SQL Server

In this tutorial, we have learned about the ROW_NUMBER function in SQL Server, Different examples related to SQL Server Row_Number, and will cover the following topics.

• SQL Server Row_Number
• SQL Server Row_Number partition
• SQL Server Row_Number over
• SQL Server Row_Number without over
• SQL Server Row_Number without order by
• SQL Server Row_Number dense_rank
• SQL Server Row_Number in where clause
• SQL Server Row_Number starting value
• SQL Server Row_Number group by