ROW_NUMBER() function in SQL

Lets learn a very basic feature in SQL today.

ROW_NUMBER()

If i have to put a serial number for my result retrieved through a SELECT Statement, i can use Row_Number() function for the same.

Example:

Just consider i have a table like below.

 CREATE TABLE [dbo].[TESTTABLE]( [STATUS] [nvarchar](50) NULL )

insert into TESTTABLE values ('GOPI'), ('DINESH'), ('SIVA')

Now we have a table with three rows and a single column which has Status as the field in it.

If we put SELECT * FROM TESTTABLE, we will get the following result

STATUS
-----------------------
GOPI
DINESH
SIVA

Now, when I apply the Row_Number() function in it as follows,

SELECT ROW_NUMBER() over(order by status) [Row], * FROM TESTTABLE

My output will be as below,

Row                    STATUS
----------------- --------------------------------------------------
1                          DINESH
2                          GOPI
3                          SIVA

Now, this is ROW_NUMBER() function, it adds the number of rows retrieved in a sequencial way for each rows.

For using ROW_NUMBER() function, we have to use OVER key word in which we have to provide a order by clause for a column name of the table on which we put the select query.

Having said this, now if you want to start the ROW number sequence from a certain value what can you do? Just add the required integer value on the Row_number function result as below,

SELECT ROW_NUMBER() over(order by status) + 2 [Row] , * FROM TESTTABLE

The result will be as below,

Row STATUS
----- ----------
3      DINESH
4      GOPI
5      SIVA

Hope this would help you....