Row_Number function in SQL

hi,

Please refer...

http://msdn.microsoft.com/en-us/library/ms186734.aspx
http://www.codeproject.com/Articles/308281/How-to-Use-ROW_NUMBER-to-Enumerate-and-Partition-R

hope this will help you.

Hi,
Row_number is used to get sequential Number of Rows. for more detail refer following link. http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/ . blog.sqlauthority.com is one of the great site to learn more about sql server. Best Luck Happy Coding:

Row_number is an inbuilt function which returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax:
ROW_NUMBER ( )  OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Whenever u use row_number you have to use Over clause and Order by clause.
Both are mandatory. Partition by is optional.


Check this.

create table emptable
(EmpId int Primary key,
EmpName varchar(50),
DeptId int)

insert into emptable values(1,'santhosh',101)
insert into emptable values(2,'Kumar',101)
insert into emptable values(3,'Amit',102)
insert into emptable values(4,'Vijay',102)
insert into emptable values(5,'Ajith',102)
insert into emptable values(6,'Ram',103)

select EMpid,empName,deptid,ROW_NUMBER() over (order by empid) as rowno from emptable
This will return me

EMpid  empName  deptid  rowno
1  santhosh  101  1
2  Kumar  101  2
3  Amit  102  3
4  Vijay  102  4
5  Ajith  102  5
6  Ram  103  6


Using partition

select EMpid,empName,deptid,ROW_NUMBER() over (partition by deptid order by empid) as rowno from emptable

This will return me rownumber sequence for each deptid.

EMpid    empName    deptid    rowno
1    santhosh    101    1
2    Kumar    101    2
3    Amit    102    1
4    Vijay    102    2
5    Ajith    102    3
6    Ram    103    1