SQL Commands


MSSQL RANK Tutorial

MSSQL RANK function is used to rank the repeating values in a manner such that similar values are ranked the same. In other words, rank function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

MSSQL RANK Function SYNTAX

RANK ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

The 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 Rank function.

MSSQL RANK FUNCTION EXAMPLE

Example 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

Example of using MSSQL RANK function without using partition_by_clause from “Employee” table:

SELECT EmployeeID, EmployeeName, Gender, Location,
RANK() OVER (order by Gender DESC)AS Rank
FROM Employee;

MSSQL RANK FUNCTION Results

This will give the following rank results.

Employee ID Employee Name Age Gender Location Salary
1001 Henry 54 Male New York
1
1003 John 24 Male London
1
1004 Mile 31 Male London
1
1002 Tina 36 Female Moscow
4
1005 Tara 26 Female Moscow
4
1006 Sophie 29 Female London
4

MSSQL RANK FUNCTION EXAMPLE 2

MSSQL RANK Function with partition_by_clause from “Employee” table above.

SELECT EmployeeID, EmployeeName, Gender, Location,
RANK() OVER (partition by Location, order by Gender DESC)AS Rank
FROM Employee;

RANK Function Results Example 2

MSSQL RANK function using partition_by_clause will provide the following results.

Employee ID Employee Name Gender Location Salary
1003 John Male London
1
1004 Mile Male London
1
1006 Sophie Female London
3
1002 Tina Female Moscow
1
1005 Tara Female Moscow
1
1001 Henry Male New York
1

 

BookMark This Page




EnglishFrenchGermanItalianPortugueseRussianSpanish