Getting Row Numbers in SQL Server

Quiet rarely, you might come upon a need to create a sequential numbering of all the rows within a query result set. I believe, both MS SQL Server and MySQL do not support row numbering. In Oracle, there is a function called ROW_NUMBER (if my memory is correct) to return the row number.

In the age of RDBMS, the concept of Row Numbers does not make much sense, because there is no specific ordering of tables. It is more correct to say that "Row Number" is the position of a row within a given query resultset.

Searching the net, I found lots of ways to number the resultset. Most of these contain very complex techniques. But, the best method is to iterate the result set (through program or stored procedure, depending on your need) and add row numbering.

Here I am going to discuss a simple technique using COUNT function. This is probably not the best way to do it. But for small tables, I guess this would do fine.

Lets take the table:



The technique I will be discussing here will only be useful for query results "ordered" on some column. Also, for performance, it should be ordered on an indexed numeric field.

In our example, the table is ordered on the UserID field. To get the row number of the first row, we run a COUNT(*) sub-query with condition that UserID <= 6. The result would naturally be 1. Similarly, COUNT(*) with UserID <=8 will return 2. Writing the whole thing into a single query:

Select UserID, Name, (Select Count(*) From Users Where UserID <= usr.UserID)
From Users

The query should return:



What Next?

In the next installment of this topic, I will be discussing about using row numbering within grouped data.