Row_Number() in SQL Server

The row number function returns the sequitial number of the row within the partition of the result set. this is one of the Ranking function in SQL Server.

ROW_NUMBER() OVER ( [PARTITION BY {,}] ORDER BY )

The Row_Number() function sorts the result set as per the column specified in the ORDER BY clause and starts numbering the row with a intial value of 1. The PARTITION BY is a optional parameter. if it is used the result is partioned as per the column or expression specified and starts numbering the row with intial value of 1 for each partition. partioning is similar to group functionality. it groups the result set and arranges in order and serialize the row numbers.

--EXAMPLE WITHOUT PARTITION
SELECT orderid, customerid, 
  ROW_NUMBER() OVER(ORDER BY orderid) AS num 
FROM orders
WHERE orderid < 10400
AND   customerid <= 'BN'

--EXAMPLE WITH SINGLE PARTITION
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (partition by OrderDate order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader ) 
SELECT * 
FROM OrderedOrders 

--EXAMPLE WITH MULTIPLE PARTITION
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (partition by Datepart(yy,OrderDate), Datepart(mm,OrderDate), Datepart(dd,OrderDate) order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader ) 
SELECT * 
FROM OrderedOrders