Dynamically number or rank rows in SQL Server using SELECT statement

By using a SELECT statement we can dynamically number or rank rows. This method is the only possible solution and which is faster than the procedural solution. Row numbering or ranking is a typical procedural issue. Typical solutions are based on loops, cursors and temporary tables. But this technique is based on an auto join. The chosen relationship is typically "is greater than.". Count how many times each element of a particular set of data fulfills the relationship "is greater than" when the set is compared to itself.

In SQL Server 2000

SELECT SerialNo = count(*), A.FirstName FROM Employee A, Employee B
WHERE A.FirstName >= B.FirstName
GROUP BY A.FirstName
ORDER BY SerialNo

Result :SerialNo FirstName
1 Abhilash
2 Aby
3 Admin
4 Anand
5 Aneesh
6 Arun
7 Justin
8 Neethu
9 Roshin
10 Suresh

In the above sample, 'FirstName' column of 'Employee' table is selected with a dynamic number. Here the relationship used is 'greater than or equal to'. The technique is counting the number of times the 'FirstName' of 'A' is greater than or equal to the 'FirstName' in 'B'. If duplicte exist in 'FirstName' use relationship 'A.FirstName + A.LastName >= B.FirstName + B.LastName'

 

In SQL Server 2005
Use ranking functions that are provided as a new feature in SQL Server 2005. Ranking functions return a ranking value for each row in a partition. Depending on the Rank function used, some rows might receive the same value as other rows.
Transact-SQL provides the following ranking functions:

• RANK
• DENSE_RANK
• ROW_NUMBER
• NTILE

SELECT RANK() OVER (ORDER BY A.Firstname) AS SerialNo, A.Firstname FROM Employee A ORDER BY SerialNo

SELECT DENSE_RANK() OVER (ORDER BY A.Firstname) AS SerialNo, A.Firstname FROM Employee A ORDER BY SerialNo

 
SELECT ROW_NUMBER() OVER (ORDER BY A.Firstname) AS SerialNo, A.Firstname FROM Employee A ORDER BY SerialNo

SELECT NTILE(10) OVER (ORDER BY A.Firstname) AS SerialNo, A.Firstname FROM Users A ORDER BY SerialNo

Here Argument to NTITLE is a positive integer constant that specifies the number of buckets into which each partition must be divided.