All Products
Search
Document Center

RANK

Last Updated: Jun 18, 2021

The RANK function determines the rank of a group of values based on the ORDER BY expression in the OVER clause. If the same sort values are available, the same rank is generated, and the number of rows that have the same values is recorded to the next rank.

Syntax

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

Parameters

Parameter

Description

OVER

Uses the OVER clause to define a window for calculation.

PARTITION BY [col1, col2..]

Specifies the column for which the window is opened.

ORDER BY col1[asc|desc]

Specifies the value by which data is ranked.

expr_list

The numeric type or the types that can be converted to the numeric type.

order_list

Defines the data column based on which values are ranked.

Examples

To create the course table and insert data into the name and grade columns, execute the following statements:

CREATE TABLE course
(
  name VARCHAR(8),
  grade NUMBER
);
INSERT INTO course VALUES('Linda',50);
INSERT INTO course VALUES('Tan',85);
INSERT INTO course VALUES('Tom',90);
INSERT INTO course VALUES('John',95);
INSERT INTO course VALUES('Mery',55);
INSERT INTO course VALUES('Peter',60);
INSERT INTO course VALUES('Jack',65);
INSERT INTO course VALUES('Rose',70);
INSERT INTO course VALUES('Tonny',75);
INSERT INTO course VALUES('Apple',80);
COMMIT;

Execute the following statement:

SELECT name,grade ,RANK() over(ORDER BY grade DESC) FROM course;

The following query result is returned:

+-------+-------+------------------------------+
| NAME  | GRADE | RANK()OVER(ORDERBYGRADEDESC) |
+-------+-------+------------------------------+
| John  |    95 |                            1 |
| Tom   |    90 |                            2 |
| Tan   |    85 |                            3 |
| Apple |    80 |                            4 |
| Tonny |    75 |                            5 |
| Rose  |    70 |                            6 |
| Jack  |    65 |                            7 |
| Peter |    60 |                            8 |
| Mery  |    55 |                            9 |
| Linda |    50 |                           10 |
+-------+-------+------------------------------+