Tuesday, December 15, 2009

Row_Number

In older SQL versions , to create row numbers we had to create a temprari tble with identity column

SELECT ROWID=IDENTITY(int,1,1) , OrderNo
INTO Order2 FROM Orders ORDER BY OrderDate


SQL 2005 and later support Row_Number that returns a squential number of a row in specifed order.

SELECT OrderNo, ROW_NUMBER() OVER(ORDER BY OrderDate DESC) AS 'RowNumber'
FROM Orders

Partition clause takes us one step further and numbers Rows in squential order for each patition.

SELECT OrderNo, State, ROW_NUMBER() OVER(Partition by State ORDER BY OrderDate DESC) AS 'RowNumber'
FROM Orders

1001, IL, 1
1005, IL, 2
1007, IL, 3
1002, IN, 1
1003, IN, 2