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”.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s