Row_Number function in SQL

Question

What is ROW_NUMBER function in SQL.

Jignesh Trivedi · Answer

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.

nilesh Patil · Answer

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:

Santhosh Kumar Jayaraman · Answer

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 EMpidempNamedeptidROW_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 EMpidempNamedeptidROW_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