ROW_NUMBER Function sql server

ROW_NUMBER is a window function. It is an important function when we are deleting or identifying the duplicate records from a table or when we are doing the pagination (below SQL server 2012 version) in SQL server

Read:

Deleting Duplicate records from a table:

https://bageshkumarbagi-msbi.blogspot.com/2015/11/deleting-duplicate-records-from-table.html

When we are using the ROW_NUMBER function, it will assign the value 1 for the first row and it increase by 1 subsequently row. As we know this function is window function, The Row number only exists with the query result set, not with the table.

It is working with the over and order by clause.

Syntel of the ROW_NUMBER

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

Partition by clause is optional.

Over: specify the order of the row

Order by: provide the sort order for the records.

See the examples

For the demo I am using the “AdventureWorks2012” Database

SELECT CustomerID, FORMAT(OrderDate,'yyyy-MM-dd') AS OrderDate, SalesOrderID, ROW_NUMBER() OVER(ORDER BY CustomerID) AS RowNumber FROM Sales.SalesOrderHeader;

See the result

   

Order by clause

The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It is required

Now we want to get the row number based on each customer ID. In this case, we need to use PARTITION BY clause.

See the SQL script

SELECT CustomerID, FORMAT(OrderDate,'yyyy-MM-dd') AS OrderDate, SalesOrderID, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS RowNumber_For_Each_customer FROM Sales.SalesOrderHeader;

It will return the row number for each customer

    

PARTITION BY clause

Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. Value expression specifies the column by which the result set is partitioned. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

Row_number() is non-deterministic, if we don't use order by clause as column. it means there may be changes to get the different row number for the same row in the different run. If we want to generate row number but don't care about the order. we can use a subquery selecting a literal value in place of a column name. see the below example. 

select customerID,SalesOrderID, ROW_NUMBER() OVER(ORDER BY(select 1)) AS RowNumber_For_Each_customer FROM Sales.SalesOrderHeader;