SQL Server Row_Number

In this sql server tutorial, we will learn about the SQL Server ROW_NUMBER function, Different examples related to SQL Server Row_Number. I have used sql server 2019 Express and sql server Management Studio for all the examples.

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. For the first row in each partition, the row number begins with 1. The 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 divides the result set into groups of rows or partitions. 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 argument is optional. 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 specifies the logical order in which the rows of the result set are organized within each partition.
• The ORDER BY clause is mandatory because the ROW NUMBER() function is order-sensitive.

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

Example

Consider the sample table below, for example, which does not have any numeric order. We will temporarily use the Row_Number() function to add the row number to 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 divides 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 use the Row_Number() function within the SELECT statement.
• In the Row_Number() function, we are using the Partition By and Order By clauses on the City column.
• So, the function will first order the table based on City names, assign a numeric value to a record based on the values in the City column, and return the following result.

SQL Server Row_Number Over

The Over clause is an important part of the Row_Number() function. It determines 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 two main clauses: PARTITION BY and ORDER BY. The PARTITION BY clause divides or partitions the data, and the ORDER BY clause arranges data in a specific order.

To execute the Row_number function on a table, the Over clause must be used, 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, we first select all the columns of the employee table using the SELECT statement. After this, we use the Row_number() function, and with this, we use 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 generates a serial number for a given record set. 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 the 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 the 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. If we try to ignore it, the SQL Server will raise an error.

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

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

In the above query, we use the “SELECT 100” statement instead of defining any column name with an Order By clause. 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 a result set partition. 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 on the Department names available in the Dept column. 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 the 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 the Row_Number() function, like 101, 901, etc.? For this implementation, we can follow the syntax given below.

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

The number in the above syntax represents the integer value from which we want to start the Row_Number() function. We add the row number value for each record to (number—1). So, using a basic mathematical operation (number—1 + 1), the Row_Number() function will start from the number we specify.

Now, for demonstration, consider the example given 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 easiest and most efficient way to use the result of the Row_Number() function in the WHERE clause is to use the CTE.

The standard 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

Consider the following example below to demonstrate the use of the Row_Number() function’s result in the WHERE clause.

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 define a CTE expression named “MyTable”.
• In the expression, we have defined the Row_Number() function within the SELECT statement and are selecting the Name and Dept columns.
• After this, we use the SELECT statement to project the result of the MyTable expression and then use the alias name of the 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 the Row_Number() function, we use the Partition By clause to partition the data in the given table. Based on that partition, the Row_Number() function will assign integer values to each record starting from 1.

On the other hand, the Group By statement in SQL Server is used to group rows that have the same values. There are very few situations where we have to use the Row_Number() function and the Group By statement together.

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

Here is the sample data for the table that we are going to use in the illustration. The table’s name is tblCustomers.

The above table will quickly note multiple records from the same country and city—for example, New York, Vancouver, etc. Now, we will use the Row_number and GROUP BY state to fetch 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, we first use the Row_Number() function to order the City column. We are fetching the values of the city and country columns. After this, we use the GROUP BY statement to find unique city and country names 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. 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], ...
)

• Like Row_Number(), the Dense_Rank() function also contains two arguments.
• The first is the Partition By clause used to partition the data
• The second is the Order By clause, which defines the order in which data should appear.
• The Partition By clause is not necessary. 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 use the Dense_Rank() function to assign a rank to each record in the table based on 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 with duplicate values, the Row_Number() function will always produce a unique integer value for each record.
• The 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 and different examples related to SQL Server Row_Number.