SQL puzzle: Find unique specialists in every department

Puzzle of the day.
This is a fairly simple problem but from time to time I am being approached by developers who need help with very similar problems.

Find all employees who has a unique job title in their respective department.

Solution #1: Using NOT EXISTS

SELECT ename, deptno, job, sal
FROM emp a
WHERE NOT EXISTS(SELECT 1
                 FROM emp b
                 WHERE a.deptno=b.deptno
                   AND a.job=b.job
                   AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #1.1 – Generic substitution for NOT EXISTS

SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT COUNT(b.empno)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job
           AND a.empno!=b.empno)
ORDER BY deptno, job

Solution #2: Using NOT IN

SELECT ename, deptno, job, sal
FROM emp a
WHERE job NOT IN(SELECT job
                 FROM emp b
                 WHERE a.deptno=b.deptno
                   AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #2.1: Using NOT IN with Multi-column subquery – it is pretty much the same as Solution #2.

SELECT ename, deptno, job, sal
FROM emp a
WHERE (job, deptno) NOT IN(SELECT job, deptno
                           FROM emp b
                           WHERE a.empno!=b.empno)
ORDER BY deptno, job

Solution #3.1: Using COUNT in subquery (very similar to Solution #1.1 but has different execution plan)

SELECT ename, deptno, job, sal
FROM emp a
WHERE 1=(SELECT COUNT(b.empno)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #3.2: A mixed version of Solutions #1.1 and #3.1:

SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT SUM(CASE WHEN a.empno=b.empno THEN 0 ELSE 1 END)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job)
ORDER BY deptno, job

Solution #4: Using Analytical function COUNT

WITH x AS (
SELECT ename, deptno, job, sal, COUNT(*) OVER(PARTITION BY deptno, job) cnt
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE cnt=1
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #4.1 – using MIN/MAX analytical functions – essentially, it is the same as solution #4

WITH x AS (
SELECT ename, deptno, job, sal, 
       MAX(empno) OVER(PARTITION BY deptno, job) max_no,
       MIN(empno) OVER(PARTITION BY deptno, job) min_no
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE max_no=min_no
ORDER BY deptno, job

Solution #5: Using In-Line view (WITH)

WITH x AS (
SELECT deptno, job
FROM emp
GROUP BY deptno, job
HAVING COUNT(*)=1
)
SELECT ename, deptno, job, sal
FROM emp JOIN x USING (deptno, job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

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