Как вернуть значение столбца: первое, второе, третье и.т.д перед максимальным значением?
Допустим есть таблица test_for_find со следующими значениями столбца:

serial_key
----------
1
3
2
7
4
5
6

Необходимо написать запрос, который должен возвратить число 6,
как первое перед максимальным (число 7);число 5 как второе перед максимальным и.т.д

Условие: без использования PL/SQL и подзапросов.
--------------------------------------------------------------------------------------
Для наилучшего понимания создадим таблицу, заполним данными и создадим индекс:
create table test_for_find
(
serial_key NUMBER(16)
);

insert into test_for_find values(1);
insert into test_for_find values(3);
insert into test_for_find values(2);
insert into test_for_find values(7);
insert into test_for_find values(4);
insert into test_for_find values(5);
insert into test_for_find values(6);
commit;

create unique index i1_test_for_find on test_for_find(serial_key);

Как известно для определения максимального значения столбца, используется функция max().
Что-бы определить максимальное значение необходим следующий запрос:
select max(serial_key) from test_for_find
Выполним:
SQLWKS> select max(serial_key) from test_for_find
2>
MAX(SERIAL
----------
7
Выбрана 1 строка.

Но нам необходимо не максимальное, а значение перед максимальным:
select
max(serial_key)
from
test_for_find
where
serial_key != (select max(serial_key) from test_for_find);

Выполним:
SQLWKS> select
2> max(serial_key)
3> from
4> test_for_find
5> where
6> serial_key != (select max(serial_key) from test_for_find)
7>
8>
MAX(SERIAL
----------
6
Выбрана 1 строка.

Однако непонятно, как определить второе перед максимальным,
тем более что уже было нарушено условие - "без подзапросов"?
Очевидно что в условии запроса, должно быть дополнительное условие,
которое бы указывало на порядковый номер этого значения.
Как известно, для упорядочивания используется фраза "order by". С её помошью можно упорядочить
данные по убыванию, а используя функцию ROWNUM - можно ограничить количество строк.
Если упорядочить данные по убыванию и указать ROWNUM<4, то запрос должен вернуть три последних
значения:7,6,5. Применив же функцию min() - мы определим, что второе перед максимальным - это 5.
Запрос же такой:
select
min(serial_key)
from
test_for_find
where
rownum<4
order by
serial_key desc;

Однако, на практике убеждаемся что, это не так:
SQLWKS> select
2> min(serial_key)
3> from
4> test_for_find
5> where
6> rownum<4
7> order by
8> serial_key desc;
MIN(SERIAL
----------
1
Выбрана 1 строка.

Почему же не сработала логика наших рассуждений? Всё дело в том, что ROWNUM срабатывает раньше,
чем упорядочивание(т.е действие фразы order by). Поэтому определение минимального значения происходит
из случайного набор данных:
SQLWKS> select
2> serial_key
3> from
4> test_for_find
5> where
6> rownum<4
7> order by
8> serial_key desc;
SERIAL_KEY
----------
3
2
1
Выбрано 3 строк.


Отсюда вывод, что-бы использовать ROWNUM, нам необходимо иметь заранее упорядоченный список значений.
Для это изменим запрос так:
select
min(serial_key)
from
(select serial_key from test_for_find order by serial_key desc)
where
rownum<4;
Выполним:
SQLWKS> select
2> min(serial_key)
3> from
4> (select serial_key from test_for_find order by serial_key desc)
5> where
6> rownum<4
7>
8>
MIN(SERIAL
----------
5
Выбрана 1 строка.

Теперь запрос работает правильно. Однако, опять же нарушается правило "без подзапроса".
Что же делать, где еще можно взять такой список? - конечно из индекса!!!

Для того что-бы правильно решить задачу необходимо использовать хинты. А точнее хинт, который
позволяет конкретно указать на использование индекса,в котором данные уже упорядочены по убыванию.
Для того что-бы задействовать индекс, столбец serial_key обязательно должен присутствовать во фразе "WHERE":
select /*+ INDEX_DESC(test_for_find i1_test_for_find) */
min(serial_key)
from
test_for_find
where
serial_key is not null and
rownum<4;
Выполним:
SQLWKS> select /*+ INDEX_DESC(test_for_find i1_test_for_find) */
2> min(serial_key)
3> from
4> test_for_find
5> where
6> serial_key is not null
7> and
8> rownum<4;
MIN(SERIAL
----------
5
Выбрана 1 строка.

Третий перед максимальным:
SQLWKS> select /*+ INDEX_DESC(test_for_find i1_test_for_find) */
2> min(serial_key)
3> from
4> test_for_find
5> where
6> serial_key is not null
7> and
8> rownum<5;
MIN(SERIAL
----------
4
Выбрана 1 строка.

Запрос работает правильно и выполнено условие задачи.
и.т.д.