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:
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; |
