MariaDB starting with 10.2

The RANK() function was first introduced with window functions in MariaDB 10.2.0.

Syntax

RANK() OVER (
  [ PARTITION BY partition_expression ]
  [ ORDER BY order_list ]
) 

Description

RANK() is a window function that displays the number of a given row, starting at one and following the ORDER BY sequence of the window function, with identical values receiving the same result. It is similar to the ROW_NUMBER() function except that in that function, identical values will receive a different row number for each result.

Examples

The distinction between DENSE_RANK(), RANK() and ROW_NUMBER():

CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));

INSERT INTO student VALUES 
     ('Maths', 60, 'Thulile'),
     ('Maths', 60, 'Pritha'),
     ('Maths', 70, 'Voitto'),
     ('Biology', 60, 'Bilal'),
     ('Biology', 70, 'Roger');

SELECT RANK() OVER (PARTITION BY course ORDER BY mark) AS rank,
    DENSE_RANK() OVER (PARTITION BY course ORDER BY mark) AS dense_rank,
    ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark) AS row_num,
    course, mark, name from student;
+------+------------+---------+---------+------+---------+
| rank | dense_rank | row_num | course  | mark | name    |
+------+------------+---------+---------+------+---------+
|    1 |          1 |       1 | Maths   |   60 | Thulile |
|    1 |          1 |       2 | Maths   |   60 | Pritha  |
|    3 |          2 |       3 | Maths   |   70 | Voitto  |
|    1 |          1 |       1 | Biology |   60 | Bilal   |
|    2 |          2 |       2 | Biology |   70 | Roger   |
+------+------------+---------+---------+------+---------+

Comments

Comments loading...
Loading