скобки), возвратят наименьшую и наибольшую заработные платы таблицы соответственно. Результаты представлены ниже:

select ename,sal,

nvl(lead(sal)over(order by sal),min(sal)over()) forward, nvl(lag(sal)over(order by sal),max(sal)over()) rewind from emp

Другое полезное свойство LAG OVER и LEAD OVER - возможность задавать, как далеко вперед или назад требуется уйти. В примере данного рецепта мы заглядываем только на одну строку вперед или назад. Так же просто перейти на три строки вперед и пять строк назад; просто задаем значения 3 и 5, как показано ниже:

select ename,sal,

lead(sal,3)over(order by sal) forward, lag(sal,5)over(order by sal) rewind from emp

Ранжирование результатов Задача

Требуется ранжировать заработные платы в таблице EMP, учитывая при этом дублирующиеся значения. Должно быть получено следующее результирующее множество:

Решение

Ранжирующие функции чрезвычайно упрощают ранжирующие запросы. Особенно полезны в данном случае три функции: DENSE

RANK OVER, ROW NUMBER OVER и RANK OVER.

DB2, Oracle и SQL Server

Поскольку требуется учитывать дубликаты, используйте ранжирующую функцию DENSE RANK OVER:

1 select dense rank() over(order by sal) rnk, sal

2 from emp

MySQL и PostgreSQL

Пока не введены ранжирующие функции, для ранжирования заработных плат используйте скалярный подзапрос:

1 select (select count(distinct b.sal)

2 from emp b

3 where b.sal <= a.sal) as rnk,

4 a.sal

5 from emp a

Обсуждение

DB2, Oracle и SQL Server

Здесь всю работу выполняет ранжирующая функция DENSE RANK OVER. В скобках после ключевого слова OVER располагается конструкция ORDER BY, определяющая порядок ранжирования строк. В решении используется выражение ORDER BY SAL, таким образом, строки таблицы EMP ранжируются в порядке возрастания заработной платы.

MySQL и PostgreSQL

Результат решения с использованием скалярного подзапроса аналогичен результату решения с DENSE RANK, потому что управляющий предикат в скалярном подзапросе определяется по SAL.

Исключение дубликатов Задача

Требуется выбрать из таблицы EMP разные типы должностей, без дублирования. Результирующее множество должно быть таким:

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

Решение

Все СУБД поддерживают ключевое слово DISTINCT, и его применение, вероятно, является самым простым способом исключения дубликатов из результирующего множества. Однако в этом рецепте также будут рассмотрены еще два метода, позволяющие избавиться от дубликатов.

DB2, Oracle и SQL Server

Конечно, традиционный метод с использованием DISTINCT и иногда GROUP BY (как видно далее в решении для MySQL/PostgreSQL) подходит для этих СУБД. Ниже представлено альтернативное решение, в котором применяется ранжирующая функция ROW NUMBER OVER:

1 select ]ob

2 from (

3 select ]ob,

4 row number()over(partition by job order by job) rn

5 from emp

6 ) x

7 where rn = 1