Теперь каждую заработную плату можно сравнить с наибольшей и наименьшей для отдела (по DEPTNO) и должности (по JOB). Обратите внимание, что группировка (включение нескольких столбцов в оператор SELECT) не оказывает влияния на возвращаемые функциями MIN OVER и MAX OVER значения. В этом прелесть оконных функций: агрегат вычисляется для заданной группы или сегмента и возвращается в каждой строке соответствующей группы. Последний шаг - просто поместить оконные функции во вложенный запрос и выбрать только те строки, которые соответствуют возвращаемым ими значениям. Для отображения статуса служащих в окончательном результирующем множестве используйте простое выражение CASE:

select deptno,ename,job,sal,

case when sal = max by dept then TOP SAL IN DEPT when sal = min by dept

then LOW SAL IN DEPT

end dept status,

case when sal = max by job

then TOP SAL IN JOB

when sal = min by job

then LOW SAL IN JOB

end job status from (

select deptno,ename,job,sal,

max(sal)over(partition by deptno) max by dept,

max(sal)over(partition by job) max by job,

min(sal)over(partition by deptno) min by dept,

select deptno,ename,job,sal,

max(sal)over(partition by deptno) maxDEPT,

max(sal)over(partition by job) maxJOB,

min(sal)over(partition by deptno) minDEPT,

min(sal)over(partition by job) minJOB

min(sal)over(partition by job) min by job from emp ) x

where sal in (max by dept,max by job, min by dept,min by job)

PostgreSQL и MySQL

Первый шаг - с помощью скалярных подзапросов находим наибольшие и наименьшие заработные платы по DEPTNO и JOB.

select e.deptno,e.ename,e.job,e.sal, (select max(sal) from emp d

where d.deptno = e.deptno) as maxDEPT, (select max(sal) from emp d

where d.job = e.job) as maxJOB, (select min(sal) from emp d

where d.deptno = e.deptno) as minDEPT, (select min(sal) from emp d

where d.job = e.job) as minJOB from emp e

Теперь все заработные платы, представленные в таблице EMP, можно сравнить с наибольшими и наименьшими для отдела (по DEPTNO) и должности (по JOB). Последний шаг - поместить скалярные подзапросы во вложенный запрос и просто выбрать служащих, заработные платы которых соответствуют возвращаемым скалярными подзапросами. Для отображения статуса служащих в окончательном результирующем множестве используйте выражение CASE:

select deptno,ename,job,sal,

case when sal = max by dept

then TOP SAL IN DEPT

when sal = min by dept

then LOW SAL IN DEPT

end as dept status,

case when sal = max by job

then TOP SAL IN JOB

when sal = min by job then LOW SAL IN JOB

end as job status from (

select e.deptno,e.ename,e.job,e.sal, (select max(sal) from emp d

where d.deptno = e.deptno) as max by dept, (select max(sal) from emp d

where d.job = e.job) as max by job, (select min(sal) from emp d

where d.deptno = e.deptno) as min by dept, (select min(sal) from emp d

where d.job = e.job) as min by job from emp e ) x

where sal in (max by dept,max by job, min by dept,min by job)