Row_Number generates a run-time column in the result set which generates sequential number to each row according to the column used in order by clause.

Syntax

Row_Number() over (order by col1, col2, ....n partition by col1, col2, ....n)

Let's create an Employee table on which we would see how it works.

  1. create table tblEmployee(
  2. EmpId char(7)
  3. , FirstName varchar(50)
  4. , LastName varchar(50)
  5. , Gender char(1)
  6. )
  8. insert into tblEmployee
  9. select 'EMP0001', 'Sandeep', 'Mittal', 'M' union all
  10. select 'EMP0003', 'Abhay', 'Kumar', 'M' union all
  11. select 'EMP0005', 'Priya', 'Gupta', 'F' union all
  12. select 'EMP0002', 'Reema', 'Gupta', 'F' union all
  13. select 'EMP0004', 'Ritesh', 'Kumar', 'M'
Now, we would generate a sequential number for all the employees using Row_Number,
  1. select EmpId, FirstName, LastName, Gender
  2. , row_number() over (order by EmpId) as RowNum
  3. from tblEmployee
Output

Output

Check out the result set where RowNum is generated based on EmpId provided in Order By,

Multiple columns can be used in the Order By clause,

Let's take another example where we would use multiple columns in Order By clause,
  1. select EmpId, FirstName, LastName, Gender
  2. , row_number() over (order by FirstName, LastName) as RowNum
  3. from tblEmployee
Output

result

In the next example, we will see how Partition By clause works,
  1. select EmpId, FirstName, LastName, Gender
  2. , row_number() over (partition by Gender order by EmpId) as RowNum
  3. from tblEmployee
Output

Table

Check out the result set where RowNum is generated starting from 1 for each group i.e. Gender which is provided in Partition By clause,

In Partition By clause also, we can use multiple columns as in Order By clause.