Следующий шаг - найти в каждом отделе SAL служащих, дата приема на работу которых совпадает с датой LAST HIRE. С помощью агрегатной функции MAX выбираем наибольшее значение SAL (если в один день было нанято несколько служащих):

select e.deptno, e.ename, e.sal, e.hiredate, (select max(d.sal) from emp d where d.deptno = e.deptno and d.hiredate =

(select max(f.hiredate) from emp f

where f.deptno = e.deptno)) as latest sal

from emp e order by 1, 4 desc

Oracle

Пользователям Oracle 8i Database подойдет решение для DB2. Те, кто работает с Oracle 9i Database и более поздними версиями, могут использовать решение, представленное ниже. Ключ к решению для Oracle -

оператор KEEP. Он позволяет ранжировать возвращенный сегмент/ группу строк и работать с первой или последней строкой группы. Рассмотрим решение без KEEP:

select deptno, ename, sal,

hiredate,

max(sal) over(partition by deptno) latest sal

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

Ранжирование строк для каждого DEPTNO осуществляется по одному столбцу (HIREDATE), а агрегация (MAX) выполняется по другому столбцу (SAL). Эта способность назначать ранги по одной величине и агрегировать по другой очень удобна, поскольку позволяет избежать применения дополнительных объединений и вложенных запросов, как в других решениях. Наконец, применив оператор OVER после KEEP, можно возвратить значение SAL, выбранное KEEP для каждой строки сегмента.

Или можно упорядочить строки по HIREDATE в порядке по убыванию и выбрать первое значение SAL. Сравните два приведенных ниже запроса, в результате их выполнения возвращается одно и то же результирующее множество:

select deptno,

ename, sal,

hiredate, max(sal)

keep(dense rank last order by hiredate) over(partition by deptno) latest sal from emp order by 1, 4 desc

DEPTNO ENAME

SAL HIREDATE

LATEST SAL

select deptno,

ename, sal,

hiredate, max(sal)

keep(dense rank first order by hiredate desc) over(partition by deptno) latest sal

from emp

order by 1, 4 desc

DEPTNO ENAME

SAL HIREDATE

LATEST SAL