|
RowNumber or AutoNumber Columns in SQL Select Statements
With the enhancements in T-SQL in the new version of Microsoft SQL Server, we have now the RowNumber functionality in sql Select statements. Or we can say that we have the ability of using AutoNumber columns in sql select statements. The new function which enables us to use autonumbering or row numbering in sql is ROW_NUMBER().
ROW_NUMBER() is a T-SQL (Transact-SQL) function. Row_Number() is one of the new functions named Ranking Window Functions introduced with SQL Server 2005 (Yukon).
Here is the list of Ranking Window Functions:
ROW_NUMBER
RANK
DENSE_RANK
NTILE
You can have a look at the page http://www.kodyaz.com/articles/article.aspx?articleid=19 for samples and usage syntax of Ranking Window Functions.
ROW_NUMBER, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition as a datatype of bigint. The Order By clause or the Partition By clause
Syntax for the Row_Number() function is as follows;
ROW_NUMBER ( ) OVER ( [ < partition_by clause > ] < order_by clause > )
< partition_by clause >, divides the result set which is produced by the FROM clause into partitions to which the ROW_NUMBER function is applied.
< order_by clause >, determines the order in which the ROW_NUMBER value is assigned to the rows in a partition.
Examples for Row_Number which returns auto numbers for numbering the returned record set in column named RowNum,
SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) RowNum, Product FROM Products
Or,
SELECT
ROW_NUMBER() OVER(ORDER BY Description) RowNum,
Description
FROM Cities
If you are looking select queries which will return more complex row numbers, for example numbering within groups on values of a column, etc. you should check the Rank(), Dense_Rank() or NTile() functions. Please look at the article at http://www.kodyaz.com/articles/article.aspx?articleid=19 for Rank(), Dense_Rank() and NTile().
|