## SQL Puzzle: Find top management in each department – with multiple workaround solutions, by Zahar Hilkevich

I was recently asked by a colleague at work to help him solving a problem that can be interpreted in scott’s emp table terms as the following:

In each department, list all managers and a president, but in the department where the president works, all managers (if any) should be filtered out.

If we look at all managers/president(s), we will see the following result:

```ENAME      JOB           DEPTNO
---------- --------- ----------
JONES      MANAGER           20
BLAKE      MANAGER           30
CLARK      MANAGER           10
KING       PRESIDENT         10
```

Our desired result should exclude CLARK.

Solution/Workaround #1:

```SELECT ename, job, deptno
FROM emp a
WHERE job='PRESIDENT'
OR (job='MANAGER' AND NOT EXISTS(SELECT 1
FROM emp b
WHERE b.deptno=a.deptno
AND job='PRESIDENT')
)
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30
```

This is the most straight-forward solution and it barely requires any explanation.

Workaround #2:

```WITH x AS (
SELECT ename, job, deptno,
RANK()OVER(PARTITION BY deptno
ORDER BY DECODE(job,'PRESIDENT',1,'MANAGER',2)) rk
FROM emp a
WHERE job IN ('PRESIDENT', 'MANAGER')
)
SELECT ename, job, deptno
FROM x
WHERE rk=1
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30
```

Analytical functions make the solution very simple. Here, we use custom sorting (in the ORDER BY) with the RANK function.

Workaround #3:

```WITH x AS (
SELECT ename, job, deptno,
COUNT(DISTINCT job)OVER(PARTITION BY deptno) cnt
FROM emp a
WHERE job IN ('MANAGER', 'PRESIDENT')
)
SELECT ename, job, deptno
FROM x
WHERE job='PRESIDENT' OR cnt=1
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30
```

This solution shows a use of COUNT(DISTINCT …)OVER() analytical function.

Workaround #4:

```SELECT ename, job, deptno
FROM emp
WHERE (deptno, DECODE(job,'PRESIDENT',1,'MANAGER',2)) IN
(SELECT deptno, MIN(DECODE(job,'PRESIDENT',1,'MANAGER',2))
FROM emp
GROUP BY deptno)
ORDER BY deptno

Result:
ENAME      JOB           DEPTNO
---------- --------- ----------
KING       PRESIDENT         10
JONES      MANAGER           20
BLAKE      MANAGER           30
```

Another example of the use of custom order hidden in the multi-column subquery.

There are at least 3-5 other workarounds available for this puzzle.

You will have no problems uncovering them after reading my book “Oracle SQL Tricks and Workarounds”.