MSSQL ROW NUMBER Tutorial
The ROW_NUMBER function returns the serial number of the concerned row in particular partition group. Each partition group first row starts with 1.
MSSQL ROW_NUMBER Function SYNTAX
ROW_NUMBER ( ) OVER ( [ < partition_by_clause > ] < order_by_clause> )
Using partition_by_clause splits the rows generated into different sets. If partition_by_clause is not specified, then all rows shall be treated as a single group. The priority of the ranking is determined by order_by_clause. The order_by_clause unlike partition_by_clause is essential in Row_Number function.
MSSQL ROW_NUMBER FUNCTION
Database table “Employee”
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1001 | Henry | 54 | Male | New York | 100000 |
| 1002 | Tina | 36 | Female | Moscow | 80000 |
| 1003 | John | 24 | Male | London | 40000 |
| 1004 | Mile | 31 | Male | London | 70000 |
| 1005 | Tara | 26 | Female | Moscow | 50000 |
| 1006 | Sophie | 29 | Female | London | 60000 |
Below is an example of row_number function without using partition_by_clause from “Employee” table:
MSSQL ROW_NUMBER FUNCTION Example
SELECT EmployeeID, EmployeeName, Gender, Location,
ROW_NUMBER() OVER (order by Gender DESC)AS Row
FROM Employee;
MSSQL ROW_NUMBER FUNCTION Results
MSSQL ROW_NUMBER function output without using partition_by_clause from “Employee” table.
| Employee ID | Employee Name | Gender | Location | Row |
| 1001 | Henry | Male | New York | 1 |
| 1003 | John | Male | London | 2 |
| 1004 | Mile | Male | London | 3 |
| 1002 | Tina | Female | Moscow | 4 |
| 1005 | Tara | Female | Moscow | 5 |
| 1006 | Sophie | Female | London | 6 |
MSSQL ROW_NUMBER FUNCTION Example 2
Below is an example of Row_number function with partition_by_clause from “Employee” table:
SELECT EmployeeID, EmployeeName, Gender, Location,
ROW_NUMBER() OVER (partition by Location, order by Gender DESC)AS Row
FROM Employee;
MSSQL ROW_NUMBER FUNCTION Results
MSSQL ROW_NUMBER function output using partition_by_clause will provide the following output results.
| Employee ID | Employee Name | Gender | Location | Row |
| 1003 | John | Male | London | 1 |
| 1004 | Mile | Male | London | 2 |
| 1006 | Sophie | Female | London | 3 |
| 1002 | Tina | Female | Moscow | 1 |
| 1005 | Tara | Female | Moscow | 2 |
| 1001 | Henry | Male | Moscow | 1 |
