< Значимое выражение > должно иметь целочисленное значение в диапазоне [1-3] или приводиться к нему. При этом:

RANK(1) = RANK()

RANK(2) = DENSE_RANK()

RANK(3) = ROW_NUMBER()

Для всех остальных значений < значимого выражения > функция RANK возвращает NULL.

< Значимые выражения > в < OVER-спецификации > задают столбцы выборки, по которым выполняется разбивка выборки на разделы.

< Имена столбцов > в < ORDER BY-спецификации > внутри < OVER-спецификации > задают имена ранжируемых столбцов.

Ранжирование записей – это присвоение им определенных значений (рангов).

Рангом текущей записи с учетом дубликатов является количество уже ранжированных записей (без учета текущей записи), плюс единица, т.е. значение ранга характеризует количество всех предшествующих или последующих записей другого ранга (более высокого или низкого – в зависимости от заданного упорядочивания) раздела выборки по ранжируемому значению, например:

В приведенном примере значение ранга 1 свидетельствует о том, что у данной записи в выборке нет предшествующих записей; значение ранга 2 – у текущей записи есть одна предшествующая запись более высокого ранга; значение ранга 5 – у текущей записи есть 4 предшествующих записи более высокого ранга и т.д.

Дубликатами считаются записи, имеющие одинаковые значения в ранжируемых столбцах.

Функция RANK использует логическое группирование. Это значит, что когда две или более записи в разделе имеют одинаковое значение в ранжируемом столбце, то такие записи будут иметь одинаковый ранг. Логическое группирование приводит к тому, что числа, соответствующие рангам, идут не подряд, а с промежутками.

Функция выполняет ранжирование записей только внутри разделов, определяемых с помощью < OVER-спецификации >.

create or replace table rank_example(i char(1), j int);
insert into rank_example (i,j) values ('a', 100);
insert into rank_example (i,j) values ('a', 200);
insert into rank_example (i,j) values ('b', 200);
insert into rank_example (i,j) values ('b', 200);
insert into rank_example (i,j) values ('b', 500);
insert into rank_example (i,j) values ('c', 500);
insert into rank_example (i,j) values ('c', 1000);

select i, j, rank() over (partition by i order by j)
  from rank_example;

 I J
 - -
|a|        100|          1|
|a|        200|          2|
|b|        200|          1|
|b|        200|          1|
|b|        500|          3|
|c|        500|          1|
|c|       1000|          2|

1) раздел 1:
|          a|        100|          1|
|          a|        200|          2|
Т.к. значения записей различны, то и ранги их в разделе различны.

2) раздел 2:
|          b|        200|          1|
|          b|        200|          1|
|          b|        500|          3|
Т.к. значения двух первых записей одинаковы, то и ранги их в разделе совпадают.

3) раздел 3:
|c|        500|          1|
|c|       1000|          2|
Т.к. значения записей различны, то и ранги их в разделе различны.

В этом примере все записи внутри разделов одинаковые (в сортируемом столбце), поэтому их ранги равны 1:
create or replace table rank_example(i char(1), j int);
insert into rank_example (i,j) values ('a', 100);
insert into rank_example (i,j) values ('b', 200);
insert into rank_example (i,j) values ('b', 200);
insert into rank_example (i,j) values ('b', 200);
insert into rank_example (i,j) values ('c', 500);
insert into rank_example (i,j) values ('c', 500);
insert into rank_example (i,j) values ('d', 1000);

select i, j, rank() over (partition by i order by j)
  from rank_example;

|a|        100|          1|
|b|        200|          1|
|b|        200|          1|
|b|        200|          1|
|c|        500|          1|
|c|        500|          1|
|d|       1000|          1|

Данные в разделе сортируются в соответствии с < ORDER BY-спецификацией >, а затем каждой записи присваивается числовой ранг, начиная с 1.

Ранг вычисляется при каждом изменении значений выражений, входящих в < ORDER BY-спецификацию >.

Если не задана опция ORDER BY функция RANK считает все записи в разделе (группе) одинаковыми и, соответственно, всем им присваивает ранг 1.

При ранжировании NULL-значения считаются одинаковыми.

Если столбцы для группировки и ранжирования данных не заданы, то разделом считается вся выборка, а ранжирование не выполняется, т.к. функция RANK сравнивает конкретные значения и ей надо указать столбец (или набор столбцов), данные которого предназначены для этого сравнения.

select rank() over () from rank_tst;
|          1|
|          1|
|          1|
|          1|
|          1|
|          1|
|          1|

Если столбцы для группировки заданы, а для ранжирования нет, то по умолчанию ранжирование выполняется по столбцам группировки, а т.к. в этом случае все записи внутри раздела будут иметь одинаковые значения в ранжируемых столбцах, то ранги всех записей будут равны 1:

select rank() over (partition by i) from rank_tst;
|          1|
|          1|
|          1|
|          1|
|          1|
|          1|
|          1|

В < OVER-спецификации > можно задавать выражение, по которому должны формироваться разделы ранжируемых значений. Например, так можно проранжировать все записи таблицы, не создавая в ней специального столбца, по которому выполняется разбивка выборки на разделы (на примере приведенной выше таблицы rank_tst):

select rank() over (partition by 'aaa' order by i) from rank_tst;
|          1|
|          2|
|          2|
|          2|
|          5|
|          5|
|          7|

Оценить цветовые предпочтения владельцев автомобилей производства фирмы GENERAL MOTORS.

select distinct a, b, c
  from (select make a,
               color b,
               rank() over (partition by make order by color) c
          from auto
         where make ='GENERAL MOTORS')
 order by a;
 A                 B       C
 -                 -       -
| GENERAL MOTORS | BLACK  |   1 |
| GENERAL MOTORS | BLUE   |  67 |
| GENERAL MOTORS | BROWN  |  99 |
| GENERAL MOTORS | GREEN  | 111 |
| GENERAL MOTORS | GREY   | 125 |
| GENERAL MOTORS | RED    | 165 |
| GENERAL MOTORS | WHITE  | 188 |
| GENERAL MOTORS | YELLOW | 269 |

Определить трех самых высокооплачиваемых сотрудников в каждом отделе.

CREATE OR REPLACE TABLE employee (employee_id INT, dept_id  INT, first_name VARCHAR(25), last_name VARCHAR(25), salary INT, job VARCHAR(20));
INSERT INTO employee VALUES (1111, 10, 'Martha', 'White', 4400, 'IT_PROG');
INSERT INTO employee VALUES (1112, 10, 'John', 'Black', 8800, 'IT_PROG');
INSERT INTO employee VALUES (1113, 20, 'Bill', 'Austin', 7600, 'MK_REP');
INSERT INTO employee VALUES (1114, 20, 'Diana', 'Kimes', 4300, 'MK_MAN');
INSERT INTO employee VALUES (1115, 20, 'David', 'Peters', 7600, 'IT_PROG');
INSERT INTO employee VALUES (1116, 30, 'Sibille', 'Peterson',12000,'AX_ASST');
INSERT INTO employee VALUES (1117, 30, 'Jack', 'Klein', 9900, 'MK_REP');
INSERT INTO employee VALUES (1118, 30, 'Alex', 'Armstrong', 8500, 'MK_REP');
INSERT INTO employee VALUES (1119, 30, 'Jennifer', 'May', 6700, 'AX_ASST');
INSERT INTO employee VALUES (1120, 40, 'Roy', 'Hunt', 9900, 'IT_PROG');
INSERT INTO employee VALUES (1121, 40, 'Wendy', 'Blunt', 8800, 'AX_ASST');
INSERT INTO employee VALUES (1122, 50, 'Valli', 'Begg', 7900, 'MK_MAN');
INSERT INTO employee VALUES (1123, 50, 'Pat', 'Donaldson', 4900, 'MK_MAN');

SELECT dept_id, last_name, salary, dept_rank
  FROM (SELECT dept_id,
               job,
               last_name,
               salary,
               RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) dept_rank
          FROM employee) AS part_dept
 WHERE dept_rank < = 3
 ORDER BY dept_id, dept_rank;