Row Number function in SQL

The ROW_NUMBER() function assigns the integer value to each row of the returned result set which starts at 1 and increments by 1.

Let's look into the syntax,

ROW_NUMBER() OVER ( [PARTITION BY exp1, exp2 ... expn] ORDER BY col1,col2...coln)

The ROW_NUMBER function must have an OVER clause with ORDER BY.

• ORDER BY clause is mandatory
• PARTITION BY clause is optional

Let's look into an example,

SELECT TOP 10 [PersonType]
      ,[FirstName]
      ,[LastName]
	  , ROW_NUMBER() OVER(ORDER BY FirstName) as RowNum
  FROM [AdventureWorks2019].[Person].[Person]

For the above example, we are using the [Person] table from [AdventureWorks2019] database. This statement returns the temporary row number for each row based on the order of first name column.

Using ROW_NUMBER function with PARTITION BY clause

The PARTITION BY clause breaks up the result set into partitions. Once the data is partitioned, row number is reset to 1 when the partition changes.

  SELECT [ProductID]
      ,[Name]
      ,[ProductModelID]
	  , ROW_NUMBER() OVER(PARTITION BY [ProductModelID] ORDER BY [Name]) as RowNum
  FROM [AdventureWorks2019].[Production].[Product] WHERE [ProductModelID] IS NOT NULL

As you can see from the below output, the [ProductModelID] is partitioned and row number reset to 1 when new [ProductModelID] is found.