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
