Skip to main content

Posts

Showing posts from February, 2022

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] ...