Interview Question: For each department count the number of employees who get no commission.

Interview Question:

Write a single SELECT statement that returns  the number of employees who get no commission broken down by department. (Use scott.emp table)

Level:

Intermediate

Expected Result:

DEPTNO	NO_COMM_COUNT
10	3
20	5
30	3

 Solutions

A very typical attempt to solve this problem results in the following query:

SELECT deptno, COUNT(*) no_comm_count
FROM scott.emp
WHERE comm IS NULL OR comm=0
GROUP BY deptno
ORDER BY 1

Yes, the result looks correct, but is the query correct?

The answer is NO! It would become apparent if we had a department where all employees get paid commission, so the number of those who does not would be 0.

Let’s change the requirement a bit – we will show all department and number of employees hired on Friday:

SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY')='FRI'
GROUP BY deptno 
ORDER BY 1

The result of this query is clearly not what we want:

DEPTNO	FRI_COUNT
30	2

We would expect the following instead:

DEPTNO	FRI_COUNT
10	0
20	0
30	2

Why don’t we get the departments 10 and 20? The answer is very simple – because we filter “all” those department rows with our WHERE clause. So how should we work around?

Let’s start with more intuitive but less efficient approaches – we will use the same query as before and UNION it with another query that returns “empty” departments. Essentially, the original problem transforms into a new one – find all department where no employees were hired on Friday.

Strategy #1: Using UNION ALL with multi-column non-correlated subquery:

SELECT deptno, COUNT(*) fri_count 
FROM scott.emp 
WHERE TO_CHAR(hiredate, 'DY')='FRI' 
GROUP BY deptno
UNION ALL
SELECT deptno, 0 fri_count 
FROM scott.emp
WHERE (deptno, 'FRI') NOT IN (SELECT deptno, TO_CHAR(hiredate, 'DY')
                              FROM scott.emp)
GROUP BY deptno
ORDER BY 1

DEPTNO	FRI_COUNT
10	0
20	0
30	2

Strategy #2: Using UNION ALL with ALL predicate on correlated subquery:

SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY')='FRI'
GROUP BY deptno
UNION ALL 
SELECT deptno, 0 no_comm_count 
FROM scott.emp a
WHERE 'FRI'!=ALL(SELECT TO_CHAR(hiredate, 'DY')
                 FROM scott.emp b
                 WHERE a.deptno=b.deptno) 
GROUP BY deptno
ORDER BY 1

It is apparent that the ALL predicate ensures that no employees were hired on Friday.

Now we will mimic the behavior of the UNION ALL operator using LEFT JOIN:

Strategy #3: Using LEFT JOIN:

SELECT a.deptno, COUNT(DISTINCT b.empno) fri_count
FROM scott.emp a LEFT JOIN scott.emp b ON a.deptno=b.deptno
                                      AND TO_CHAR(b.hiredate, 'DY')='FRI'
GROUP BY a.deptno
ORDER BY 1

COUNT(DISTINCT …) is needed to handle a Cartesian Product as the join by deptno column produces many to many  relationship, i.e. Cartesian product.

Strategy #4: Generic substitution technique for an outer-join using UNION ALL

WITH e AS (
SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY') = 'FRI'
GROUP BY deptno
UNION ALL
SELECT deptno, 0
FROM scott.emp
GROUP BY deptno
)
SELECT deptno, MAX(fri_count) fri_count
FROM e
GROUP BY deptno
ORDER BY 1

All the above techniques may look cool but they are clearly an overkill for such a simple problem. There is a simple rule worth remembering:

If you need to conditionally aggregate all records in the table but you fail doing so due to a WHERE clause filter, consider moving the filter into the GROUP function you use in SELECT.

Strategy #5: Conditional Aggregation

SELECT deptno, COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'FRI', 1)) fri_count
FROM scott.emp
GROUP BY deptno
ORDER BY 1

Alternatively, you can use CASE function inside of COUNT. It is especially convenient for our original question/problem, i.e. to count employees who is not paid a commission:

SELECT deptno, COUNT(CASE WHEN LNNVL(comm>0) THEN 1 END) no_comm_count
FROM scott.emp
GROUP BY deptno
ORDER BY 1

DEPTNO	NO_COMM_COUNT
10	3
20	5
30	3

This approach is the most efficient as it makes Oracle scanning the emp table only once.

Notice the use of the LNNVL function. You can read more about it in my recent post here.

My Oracle Group on Facebook:

If you like this post, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Suggested Reading:

Would you like to read about many more tricks and puzzles? For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

7 Solutions to 2018 Oracle SQL Puzzle of the Week #13

Second Top Employee as of the Start of Employment

List all employees who were 2nd top paid in the entire company as of the time their employment started

• Use a single SELECT statement only.
• At the time of employment start the rank of the employee by salary should be 2.
• Show the top salary at the time when the employee started with the company.
• We assume that no employees have ever been terminated since day 1.

Expected Result:

ENAME	JOB	SAL	HIREDATE	MAX_SAL
WARD	SALESMAN	1250	22-FEB-81	1600
BLAKE	MANAGER	2850	01-MAY-81	2975
FORD	ANALYST	3000	03-DEC-81	5000
SCOTT	ANALYST	3000	19-APR-87	5000

Solutions:

Solution #1. Using LATERAL view, RANK and cumulative MAX analytic functions (Oracle 12g+):

SELECT e.ename, e.job, e.hiredate, e.sal, r.max_sal 
FROM scott.emp e, LATERAL(SELECT a.empno,  
                                 RANK() OVER(ORDER BY a.sal DESC) rk, 
                                 MAX(a.sal) OVER() max_sal 
                          FROM scott.emp a 
                          WHERE a.hiredate<=e.hiredate) r 
WHERE e.empno=r.empno  
  AND rk=2 
ORDER BY e.hiredate

Solution #2. Using CTE, cumulative MAX analytic function and a correlated subquery with COUNT to mimic the filter by RANK:

WITH x AS ( 
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal 
FROM scott.emp a 
) 
SELECT * 
FROM x 
WHERE 1=(SELECT COUNT(*) 
         FROM scott.emp 
         WHERE hiredate<=x.hiredate 
           AND sal>x.sal) 
ORDER BY hiredate

Solution #3. Using CTE, cumulative MAX analytic function and an in-line scalar subquery in SELECT to mimic the RANK:

WITH x AS ( 
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal, 
       (SELECT COUNT(*)+1 
        FROM scott.emp 
        WHERE sal>e.sal  
          AND hiredate<=e.hiredate) rk 
FROM scott.emp e 
) 
SELECT ename, job, hiredate, sal, max_sal 
FROM x 
WHERE rk=2 
ORDER BY hiredate

Solution #4. Using self-join and Cartesian Product with aggregation:

SELECT a.ename, a.job, a.hiredate, a.sal, MAX(b.sal) max_sal 
FROM scott.emp a JOIN scott.emp b ON b.hiredate<=a.hiredate 
                                 AND b.sal>a.sal 
GROUP BY a.ename, a.job, a.hiredate, a.sal 
HAVING COUNT(DISTINCT b.empno)=1 
ORDER BY a.hiredate

Solution #5. Using CTE and cumulative MAX analytic function (twice):

WITH x AS ( 
SELECT ename, job, hiredate, sal, 
       MAX(sal) OVER(ORDER BY hiredate) max_sal 
FROM scott.emp  
), y  AS ( 
SELECT ename, job, hiredate, sal, max_sal, MAX(sal) OVER(ORDER BY hiredate) max_sal2 
FROM x 
WHERE sal<max_sal 
) 
SELECT ename, job, hiredate, sal, max_sal 
FROM y 
WHERE sal=max_sal2 
ORDER BY hiredate

Solution #6. Using regular and recursive CTEs, ROWNUM, GREATEST, and CASE functions (no Analytic functions!):

WITH e AS ( 
SELECT ename, job, sal, hiredate 
FROM scott.emp 
ORDER BY hiredate 
), x AS ( 
SELECT ename, job, sal, hiredate, ROWNUM rn 
FROM e 
), y(max_sal, sal2, rn) AS ( 
SELECT sal, 0, 1 
FROM x 
WHERE rn=1 
UNION ALL 
SELECT GREATEST(x.sal, y.max_sal) AS max_sal, 
       CASE WHEN x.sal>y.max_sal THEN y.max_sal 
            WHEN x.sal>y.sal2 AND x.sal<=y.max_sal THEN x.sal  
            ELSE y.sal2  
       END AS sal2, 
       x.rn 
FROM x JOIN y ON x.rn=y.rn+1 
) 
SELECT x.ename, x.job, x.sal, x.hiredate, y.max_sal 
FROM y JOIN x ON y.rn=x.rn AND y.sal2=x.sal

Solution #7. Using CTE and MODEL clause to mimic Solution #6:

WITH x AS ( 
SELECT * 
FROM scott.emp 
MODEL 
DIMENSION BY (ROW_NUMBER() OVER(ORDER BY hiredate) rn) 
MEASURES(ename, job, sal, hiredate, sal max_sal, 0 sal2) 
RULES( 
    max_sal[rn>1] = GREATEST(max_sal[CV()-1], sal[CV()]), 
    sal2[rn>1] = CASE WHEN sal[CV()]> max_sal[CV()-1] THEN max_sal[CV()-1] 
                      WHEN sal[CV()]> sal2[CV()-1]   
		       AND sal[CV()]<=max_sal[CV()-1] THEN sal[CV()]  
                      ELSE sal2[CV()-1] 
                 END 
     ) 
) 
SELECT ename, job, sal, hiredate, max_sal 
FROM x 
WHERE sal=sal2

You can execute the above SQL statements in Oracle Live SQL environment.
My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Further Reading:

Would you like to read about many more tricks and puzzles?

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

5 Solutions to 2018 Oracle SQL Puzzle of the Week #1

2018 Puzzle of the Week #1:

For a given text string, find the first (from the beginning) longest sub-string that does not have repeating characters.

Solutions:

Solution #1: Using CONNECT BY clause (for range generation), REGEXP_COUNT, and RANK() functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
), x AS ( 
SELECT SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1) substr, 
       RANK() OVER(ORDER BY r2.rn - r1.rn DESC, r1.rn) rk 
FROM r r1, r r2, w 
WHERE r1.rn<=r2.rn 
 AND REGEXP_COUNT(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1') = 0 
) 
SELECT substr 
FROM x 
WHERE rk=1

Result of execution in Oracle Live SQL client:

SUBSTR
rkans

Solution #2: Using CONNECT BY clause (for range generation), REGEXP_LIKE, and MAX() KEEP functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
) 
SELECT MAX(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1)) 
 KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr 
FROM r r1, r r2, w 
WHERE r1.rn<=r2.rn 
 AND NOT REGEXP_LIKE(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1')

Solution #3: Using CONNECT BY clause (twice), LATERAL view, REGEXP_COUNT, and RANK() functions:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), s AS ( 
SELECT SUBSTR(word, LEVEL) word, LEVEL rn 
FROM w 
CONNECT BY LEVEL<=LENGTH(word) 
) 
SELECT MAX(x.substr) 
       KEEP(DENSE_RANK FIRST ORDER BY LENGTH(x.substr) DESC, s.rn) substr 
FROM s, LATERAL(SELECT SUBSTR(s.word, 1, LEVEL) substr 
                FROM dual 
                CONNECT BY LEVEL<=LENGTH(s.word)) x 
WHERE REGEXP_COUNT(x.substr, '(.).*\1') = 0

Solution #4: Using XMLTable function (for range generation), Correlated subquery with COUNT(DISTINCT), and MAX() KEEP function:

WITH w AS ( 
SELECT 'arkansas' AS word 
FROM dual 
), r AS ( 
SELECT ROWNUM rn, word
FROM w, XMLTABLE('for $i in 1 to $N cast as xs:integer return $i' 
                 PASSING LENGTH(w.word) AS N) x
) 
SELECT MAX(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1))
 KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr 
FROM r r1, r r2
WHERE r1.rn<=r2.rn 
 AND r2.rn - r1.rn + 1 = 
 (SELECT COUNT(DISTINCT SUBSTR(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1), 
                               LEVEL, 1)) 
 FROM dual 
 CONNECT BY LEVEL<=r2.rn - r1.rn + 1 
 )

Solution #5: Using CONNECT BY, Recursive CTE, INSTR, SUBSTR, and MAX() KEEP functions:

WITH w AS (
 SELECT 'arkansas' word
 FROM dual
), s(sub, word, lvl, rn) AS (
SELECT SUBSTR(word, LEVEL, 1), SUBSTR(word, LEVEL) word, 1, ROWNUM
FROM w
CONNECT BY SUBSTR(word, LEVEL) IS NOT NULL
UNION ALL
SELECT SUBSTR(word, 1, lvl+1), word, lvl+1, ROWNUM
FROM s
WHERE LENGTH(SUBSTR(word, 1, lvl+1))=lvl+1
 AND INSTR(sub, SUBSTR(word, lvl+1, 1))=0
)
SELECT MAX(sub) KEEP (DENSE_RANK FIRST ORDER BY lvl DESC, rn) substr
FROM s

You can execute the above SQL statements in Oracle Live SQL environment.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

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

 

 

 

 

 

 

Three Soluitions to Puzzle of the Week #17

Puzzle of the Week #17:

Write a single SELECT statement that would show the years of hire in each department. The result should have 3 columns (see below): deptno, year1, and year2. If a department only hired during 1 calendar year, this year should be shown in year1 column (see deptno 30) and year2 column should be blank. If a department hired during 2 calendar years, the first year should be should be shown in year1 column, and the 2nd year should be shown in year2 column (see deptno 10). In all other cases, show 1st year in year1 column and “More (N)” where N is the number of years that department did the hiring (see deptno 20).

Expected Result:

DEPTNO Year 1   Year 2
------ -------- --------
    10 1981     1982
    20 1980     More (3)
    30 1981

Solutions

#1: Using COUNT(DISTINCT ..)

SELECT deptno, MIN(EXTRACT(YEAR FROM hiredate)) AS "Year 1",
       CASE COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))
            WHEN 1 THEN ''
            WHEN 2 THEN TO_CHAR(MAX(EXTRACT(YEAR FROM hiredate)))
            ELSE 'More (' || COUNT(DISTINCT EXTRACT(YEAR FROM hiredate)) || ')'
       END AS "Year 2"
FROM emp
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
    10       1981 1982
    20       1980 More (3)
    30       1981

#2: Using DENSE_RANK Analytic Function

WITH x AS (
SELECT deptno, EXTRACT(YEAR FROM hiredate) hire_year,
       DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate)) rk
FROM emp
)
SELECT deptno, MIN(hire_year) "Year 1",
       CASE MAX(rk) WHEN 1 THEN ''
                    WHEN 2 THEN CAST(MAX(hire_year) AS CHAR(4))
                    ELSE 'More (' || MAX(rk) || ')'
       END AS "Year 2"
FROM x
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
    10       1981 1982
    20       1980 More (3)
    30       1981

#3: Using PIVOT clause

SELECT deptno, Y1 "Year 1",
       CASE WHEN cnt>2 THEN 'More (' || cnt || ')'
       ELSE TO_CHAR(Y2)
       END "Year 2"
FROM
(
SELECT deptno, EXTRACT(YEAR FROM hiredate) yr,
       CASE DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate))
       WHEN 1 THEN 'Y1'
            WHEN 2 THEN 'Y2'
       END AS Y,
       COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))OVER(PARTITION BY deptno) cnt
FROM emp
)
PIVOT
(
  MAX(yr)
  FOR y IN ('Y1' y1,'Y2' y2)
);

DEPTNO     Year 1 Year 2
------ ---------- --------
    10       1981 1982
    20       1980 More (3)
    30       1981

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

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

 

 

 

 

 

Solutions to Puzzle of the Week #8

Puzzle of the Week #8:

Find job titles represented in every department. Write a single SELECT statement only.

Expected Result: (Only clerks and managers work in all 3 departments: 10,20, and 30)

JOB
--------
CLERK
MANAGER

Solutions:

#1: Two COUNT(DISTINCT ..) in HAVING

SELECT job
FROM emp
GROUP BY job
HAVING COUNT(DISTINCT deptno)=(SELECT COUNT(DISTINCT deptno) FROM emp)

#2: Analytic COUNT(DISTINCT ..) with CONNECT BY

SELECT DISTINCT job
FROM (
SELECT job, deptno, LEVEL level#, COUNT(DISTINCT deptno) OVER() cnt
FROM emp
CONNECT BY job=PRIOR job
AND deptno>PRIOR deptno
)
WHERE level#=cnt

#3: Two Analytic COUNT(DISTINCT..)

WITH x AS (
SELECT deptno, job, COUNT(DISTINCT deptno)OVER() cnt, COUNT(DISTINCT deptno)OVER(PARTITION BY job) cnt2
FROM emp
)
SELECT DISTINCT job
FROM x
WHERE cnt=cnt2

OR

WITH x AS (
SELECT deptno, job, COUNT(DISTINCT deptno)OVER() cnt, COUNT(DISTINCT deptno)OVER(PARTITION BY job) cnt2
FROM emp
)
SELECT job
FROM x
WHERE cnt=cnt2
GROUP BY job

#4: Cartesian Product and Two COUNT(DISTINCT ..)

SELECT a.job
FROM emp a, emp b
GROUP BY a.job
HAVING COUNT(DISTINCT a.deptno)=COUNT(DISTINCT b.deptno)

#5: ROLLUP with RANK OVER COUNT(DISTINCT..)

WITH x AS (
SELECT job, COUNT(DISTINCT deptno) cnt, 
       RANK()OVER(ORDER BY COUNT(DISTINCT deptno)  DESC) rk
FROM emp
GROUP BY ROLLUP(job)
)
SELECT job
FROM x
WHERE rk=1
  AND job IS NOT NULL

#6: Analytic COUNT(DITSINCT..) comparison with MINUS

WITH x AS (
SELECT job, 
       CASE WHEN COUNT(DISTINCT deptno)OVER()=COUNT(DISTINCT deptno)OVER(PARTITION BY job) THEN 1 END
FROM emp
MINUS
SELECT job, NULL
FROM emp
)
SELECT job
FROM x

#7: No COUNT(DISTINCT ..) solution:

WITH x AS (
SELECT a.deptno, b.job, NVL(COUNT(c.empno),0) idx
FROM (SELECT DISTINCT deptno FROM emp) a CROSS JOIN (SELECT DISTINCT job FROM emp) b
    LEFT JOIN emp c ON a.deptno=c.deptno AND b.job=c.job
GROUP BY a.deptno, b.job
)
SELECT job
FROM x
GROUP BY job
HAVING MIN(idx)>0

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

Solutions to Puzzle of the Week #6

Puzzle of the Week #6

Find all employees who is paid one of top 4 salaries in the entire company without using sub-queries and in-line views/WITH clause.

Expected Result:

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

 

Method/Workaround #1: Use Aggregation over Cartesian Product

This method works in all RDBMS systems, such as Oracle, SQL Server, Sybase, Teradata, etc.

SELECT a.ename, a.sal
FROM emp a, emp b
WHERE a.sal<=b.sal
GROUP BY a.ename, a.sal
HAVING COUNT(DISTINCT b.sal)<=4
ORDER BY 2 DESC;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

This method is described in details in my book Oracle SQL Tricks and Workarounds.

Method/Workaround #2: Use DENSE_RANK and MINUS

This method is more Oracle specific, but it is as good as the 1st one. It was suggested by one of the contest participants:

SELECT ename, 
       CASE WHEN DENSE_RANK()OVER(ORDER BY sal DESC)<=4 THEN sal END sal
FROM emp
MINUS
SELECT ename, NULL
FROM emp
ORDER BY 2 DESC;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

This is a very elegant variation of the following query with MINUS substituting the filter for analitic function result:

SELECT ename, sal
FROM (SELECT ename, sal, DENSE_RANK()OVER(ORDER BY sal DESC) rk
      FROM emp)
WHERE rk<=4;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

Interestingly enough, Teradata SQL has a special clause QUALIFY for that matter. It is very elegant and maybe one day it will become an ANSII standard:

SELECT ename, sal
FROM emp
QUALIFY DENSE_RANK()OVER(ORDER BY sal DESC)<=4

Finally, I would like to share a couple of more traditional approaches that DO USE subqueries:

SELECT ename, sal
FROM emp a
WHERE 4>=(SELECT COUNT(DISTINCT b.sal)
          FROM emp b
          WHERE b.sal>=a.sal)
ORDER BY sal DESC;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

SELECT ename, sal
FROM emp a
WHERE 4>(SELECT COUNT(DISTINCT b.sal)
          FROM emp b
          WHERE b.sal>a.sal)
ORDER BY sal DESC;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

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