SQL Commands


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

 

BookMark This Page




EnglishFrenchGermanItalianPortugueseRussianSpanish