SQL RANK Function Explained [Practical Examples]

Overview of SQL Rank function

SQL Rank functions are used to assign a row number to each row within a partition of a result set, SQL Rank is a SQL Server function, it is also known as the window function

Note that SQL RANK functions are assigned a temporary number to each row calculated when executing the query. In the SQL RANK functions, we use the OVER() clause to define a set of rows in the result set. We can also use SQL PARTITION BY clause to define a subset of data in a partition. You can also use Order by clause to sort the results in a descending or ascending order

The rows within a partition that have the same values will receive the same rank. The rank of the first row within a partition is one. The RANK() function adds the number of tied rows to the tied rank to calculate the rank of the next row, therefore, the ranks may not be consecutive

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5)

 

Different types of SQL RANK Function

SQL Rank functions:

1. RANK()
2. ROW_NUMBER()
3. DENSE_RANK()
4. NTILE()

 

Point to note of SQL RANK Function

• If two or more rows tie for a rank, each tied row receives the same rank
• The RANK function does not always return consecutive integers
• The sort order that is used for the whole query determines the order in which the rows appear in a result set
• SQL RANK is nondeterministic

 

SQL RANK Function Return Type

Return Type of SQL RANK function is bigint

 

SQL RANK Function Examples

Consider Hospital Management Database with four tables patient, doctor, bill, and laboratory for practical examples
Patient Table

Doctor Table

Bill Table

Laboratory Table

 

1. SQL RANK()

SQL Rank() is used to specify rank for each row in the result set, the rows within a partition that have the same values will receive the same rank. The rank of the first row within a partition is one.

The SQL Rank function adds the number of tied rows to the tied rank to calculate the rank of the next row, therefore, the ranks may not be consecutive

 

1.1 SQL RANK() Syntax

RANK() OVER (
   [PARTITION BY expression, ]
   ORDER BY expression (ASC | DESC) );

Here,

• partition_by_expression : It divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group
• order_by_expression : It determines the order of the data before the function is applied. The order_by_clause is required

 

1.2 Using SQL RANK() with Single table

Example-1: Write SQL query to display patient details in ascending order of diseases name with row numbering

SELECT patient_id,name,disease,
RANK() OVER ( ORDER BY disease) Patient_disease
FROM patient;

• In this query, Order By clause is used to sort the record in ascending order of disease name
• SQL RANK function is used to give numbering to each row of result set

OUTPUT:

 

1.3 Using SQL RANK() with SQL JOIN

Example-2: Write SQL query to display patient lab report data with row count number based on the date of the lab report

SELECT patient.patient_id,name,disease,date,lab_no,amount,
RANK() OVER ( ORDER BY date) Patient_disease 
FROM laboratory left join patient on laboratory.patient_id=patient.patient_id;

• In the above query, two display data from two table patient and laboratory SQL Left join is used on condition of common column
• Sort clause is applied on date column lab report to display the record in ascending order of lab report date
• SQL RANK function is applied on the resulting to give the row numbering

OUTPUT:

In the above two examples, because we skipped the PARTITION BY clause,the RANK() function treated the whole result set as a single partition

 

1.4 Using SQL RANK() over partitions

In the SQL RANK function partition By clause is used to make a partition of the resulting set based on the expression or column name given with the partition by clause

Example-3: Write SQL query to display each patient’s lab report data with the counter of each row based on patient record in laboratory data in the sorting order of lab report date

SELECT patient.patient_id,name,disease,date,lab_no,amount,
RANK() OVER ( PARTITION BY laboratory.patient_id ORDER BY date) Patient_count 
FROM laboratory left join patient on laboratory.patient_id=patient.patient_id;

• In this query, the SQL RANK function’s argument partition is applied to the patient id to separate each patient data
• SQL RANK Function will assign row number to each record of each patient in the laboratory table in the ascending order of lab test date

OUTPUT:

 

1.5 Using SQL RANK() over partitions with DESC

SQL RANK function’s argument SORT BY can also be used to perform sorting in descending order on the resulting record set by specifying the DESC keyword

Example-4: Write SQL query to display patient billing date on the descending order of no of admitted days also count each doctor’s patient records by displaying row number for each doctor’s admitted patient

SELECT patient.patient_id,name,bill_no,doctor_charge,room_charge,no_of_days,RANK() OVER ( 
PARTITION BY bill.doctor_id ORDER BY no_of_days desc) doctor_patient_admitted
FROM bill left join patient on bill.patient_id=patient.patient_id;

• In the above query, both arguments of SQL RANK function is applied PARTITION BY argument is applied on doctor_id column to make separation of each doctor’s admitted patient record group
• ORDER BY argument is used to perform sorting operation on no of admitted days of patient
• SQL RANK clause is assigned record number to result each set of records for each doctor_id decrement order

OUTPUT:

 

1.6 Using SQL RANK() with WHERE clause

Where clause in SQL RANK function is used to fetch record based on condition and then calculate a rank for each row within a partition of a result set

Example-5: Write SQL query to display patient billing date on the descending order of no of admitted days also count each doctor’s patient records by displaying row number for each doctor’s admitted patient whose room charges are more than 300 per day

SELECT patient.patient_id,name,bill_no,doctor_charge,room_charge,no_of_days, RANK() OVER ( PARTITION BY bill.doctor_id ORDER BY no_of_days ) doctor_patient_admitted
FROM bill left join patient on bill.patient_id=patient.patient_id where room_charge > 300;

• In this query, SQL left join is used to fetch the records from both table patient and bill
• PARTITION BY argument will make partition of resulting set based in doctor_id
• ORDER BY argument will sort the record in order of no. of days
• WHERE clause has been used to conditionally retrieve the records if room charges are more than 300
• SQL RANK function will give each record set a rank number in increment order of one

OUTPUT:

 

2. SQL ROW_NUMBER()

SQL Rank function ROW_Number() SQL RANK function is used to assign a unique sequential number for each row in the resulting recordset. It gives the rank one for the first row and then increments the value by one for each row. We get different ranks for the row having similar values as well

 

2.1 SQL ROW_Number() Syntax

ROW_Number() OVER (
   [PARTITION BY expression, ]
   ORDER BY expression (ASC | DESC) );

Here,

• partition_by_expression : It divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group
• order_by_expression : It determines the order of the data before the function is applied. The order_by_clause is required

 

2.2 SQL ROW_Number Return Type

Return Type of SQL Rank function is bigint

 

2.3 SQL ROW_Number() Examples

Example-6: Write SQL Query to fetch patient details on the sorting order of doctor id and also assign row count number using Row_Number function

SELECT patient_id,name,disease,Row_number() OVER (ORDER BY age) Patient_disease
FROM patient;

• In the above query, Order by clause is applied on age to sort the record in ascending order of age
• SQL Row_number function will assign a row number to each record in increment order

OUTPUT:

Example-7 :  Write SQL Query to display each patient laboratory report with row counting number for each patient lab report entry

SELECT patient.patient_id,name,disease,date,lab_no,amount, Row_number() OVER ( PARTITION BY laboratory.patient_id ORDER BY date) Patient_count 
FROM laboratory left join patient on laboratory.patient_id=patient.patient_id;

• In this query, Argument PARTITION BY is applied on patient id to separate each patient data in the laboratory table
• ROW_Number will assign a row number to each record of each patient in the laboratory table in the ascending order of lab test date

OUTPUT:

 

3. SQL DENSE_RANK()

The SQL DENSE_RANK() is a window function that assigns a rank to each row within a partition of a result set. Unlike the RANK() function, the DENSE_RANK() function returns consecutive rank values. Rows in each partition receive the same ranks if they have the same values

 

3.1 SQL DENSE_RANK() Syntax

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The DENSE_RANK() function is applied to the rows of each partition defined by the PARTITION BY clause, in a specified order, defined by ORDER BY It resets the rank when the partition boundary is crossed

 

3.2 SQL DENSE_RANK() Function example

Example-8:Write SQL Query to display patient details with row numbers in the descending order of age

SELECT patient_id, name, age,disease,DENSE_RANK() OVER ( ORDER BY age DESC) patinet_age 
FROM patient;

In this query, SQL DENSE_RANK  function is applied on the record set of patient data in the sorting order of patient age , row number will start with one in increment order

OUTPUT:

 

4. SQL NTILE() Function

SQL NTILE() divides the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs

 

4.1 SQL NTILE() Syntax

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )  

Here,

• integer_expression
  It is a positive integer expression that specifies the number of groups into which each partition must be divided. integer_expression can be of type int or bigint
• <partition_by_clause>
  It divides the result set produced by the FROM clause into partitions to which the function is applied.
• <order_by_clause>
  It determines the order in which the NTILE values are assigned to the rows in a partition. An integer cannot represent a column when the <order_by_clause> is used in a ranking function

 

4.2 SQL NTILE() Example

Example-9 : Write SQL query to display patient billing date on the descending order of no of admitted days also count each doctor’s patient records by displaying row number for each doctor’s admitted patient whose room charges are less than 800 per day

SELECT patient.patient_id,name,bill_no,doctor_charge,room_charge,no_of_days,
	NTILE(2) OVER ( PARTITION BY bill.doctor_id ORDER BY no_of_days ) doctor_patient_admitted
FROM bill left join patient on bill.patient_id=patient.patient_id where room_charge < 800;

OUTPUT:

 

Summary

This tutorial first covers an overview of SQL RANK Function with syntax and argument explanation and return type description, in the second part of the article we have covered the practical examples with a single table, multiple tables, with partition argument and ORDER BY ASC and DESC, also mention an example of SQL RANK Function with Where a condition

 

References

SQL LEFT JOIN
SQL  ORDER BY

 

Further Reading

SQL RANK