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 |
+-------+-------+------------------------------+