12 Solutions to 2018 Oracle SQL Puzzle of the Week #10

Top Salary Puzzle

Find highest salary in each department without using MAX function

  • Use a single SELECT statement only.
  • For an added complexity (optional): try not using ANY functions at all (neither group, nor analytic, not even scalar)

Expected Result:

DEPTNO MAX_SAL
10 5000
20 3000
30 2850

Solutions:

We will begin with a simpler problem that does allow us using functions.

Solution #1. Using MIN function

Credit to: Boobal Ganesan

MIN function can be seen as an opposite to the MAX, so it is trivial to employ it here:

SELECT deptno, -MIN(-sal) max_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1;

Solution #2. Using LISTAGG and REGEXP_SUBSTR functions

This is an “order” based approach that sorts the values within a concatenated string and then uses regular expression to cut the first token.

SELECT deptno,
       REGEXP_SUBSTR(LISTAGG(sal,',') 
                     WITHIN GROUP(ORDER BY sal DESC),'[^,]+',1,1) max_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1;

Solution #3. Using AVG(…) KEEP() group function

This is another “order” based strategy whete AVG function can be replaced with MIN or any other aggregate function that returns a single value out of a set of identical ones.

SELECT deptno, AVG(sal) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1;

Solution #4. Using Analytic function and CTE

ROW_NUMBER is chosen in this approach, though other analytic functions, such as RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, etc can be used here (with some changes) as well. ROW_NUMBER is convenient to use as it allows to avoid DISTINCT option.

WITH x AS (
SELECT deptno, sal, 
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal DESC) rn
FROM scott.emp
)
SELECT deptno, sal max_sal
FROM x
WHERE rn=1
ORDER BY 1;

Solution #5. Using MATCH_RECOGNIZE clause

Credit to: KATAYAMA NAOTO

This approach is similar to the previous one if we used LAG analytic function: which would return NULL for the top record.

SELECT deptno, sal max_sal 
FROM scott.emp
MATCH_RECOGNIZE (
PARTITION BY deptno
ORDER BY sal DESC
ALL ROWS PER MATCH
PATTERN (ISNULL)
DEFINE ISNULL AS PREV(ISNULL.sal) IS NULL
);

Solution #6. CONNECT BY and CONNECT_BY_ISLEAF while avoiding Analytic functions

This approach is a bit artificial. We could have used DISTINCT and avoid START WITH clause completely.  CTEs x and y are used to simulate ROW_NUMBER analytic function.

WITH x AS (
SELECT deptno, sal
FROM scott.emp
ORDER BY 1,2
), y AS (
SELECT x.*, ROWNUM rn
FROM x
)
SELECT deptno, sal
FROM y
WHERE CONNECT_BY_ISLEAF=1
CONNECT BY deptno=PRIOR deptno
       AND rn=PRIOR rn+1
START WITH (deptno, rn) IN (SELECT deptno, MIN(rn)
                            FROM y
                            GROUP BY deptno);

Solution #7. Using MODEL clause with ROW_NUMBER function

This method is pretty much the same as in the Solution #4 above. The RETURN UPDATED ROWS and dummy measures are used to only return rows with rn=1.

SELECT deptno, max_sal
FROM scott.emp
MODEL
RETURN UPDATED ROWS
PARTITION BY (deptno)
DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rn)
MEASURES(sal max_sal, 0 dummy)
RULES(
 dummy[1]=1
)
ORDER BY 1;

The following 5 solutions (##8-12) satisfy the “added complexity” term and do NOT use any functions at all.

Solution #8. Using ALL predicate

Generally speaking, >=ALL filter is identical to =(SELECT MAX() …). See my book for more detailed explanations.

SELECT deptno, sal max_sal
FROM scott.emp a
WHERE sal>=ALL(SELECT sal
               FROM scott.emp
               WHERE deptno=a.deptno)
GROUP BY deptno, sal
ORDER BY 1;

Solution #9. Using NOT EXISTS predicate

See Chapter 10 of my book for details.

SELECT deptno, sal max_sal
FROM scott.emp a
WHERE NOT EXISTS(SELECT 1
                 FROM scott.emp
                 WHERE deptno=a.deptno
                   AND sal>a.sal)
GROUP BY deptno, sal
ORDER BY 1;

Solution #10. Using Outer-Join with IS NULL filter

This approach is also covered very deeply in my book, Chapter 10.

SELECT a.deptno, a.sal max_sal
FROM scott.emp a LEFT JOIN scott.emp b ON a.deptno=b.deptno
                                      AND b.sal>a.sal 
WHERE b.empno IS NULL
GROUP BY a.deptno, a.sal
ORDER BY 1;

Solution #11. Using MINUS and ANY predicate

MINUS serves 2 purposes: it removes non-top rows and eliminates duplicates, so no DISTINCT option (or GROUP BY) is required.

SELECT deptno, sal max_sal 
FROM scott.emp
MINUS
SELECT deptno, sal
FROM scott.emp a
WHERE sal<ANY(SELECT sal 
              FROM scott.emp
              WHERE deptno=a.deptno);

Solution #12. Using MINUS and EXISTS predicate

Last two approaches covered in the drill from the Chapter 10 of my book.

SELECT deptno, sal max_sal 
FROM scott.emp
MINUS
SELECT deptno, sal
FROM scott.emp a
WHERE EXISTS(SELECT 1 
             FROM scott.emp
             WHERE deptno=a.deptno
               AND sal>a.sal);

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.

Advertisements

Three Solutions to Puzzle of the Week #18

Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

Expected Result:

Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933

Solutions

#1: Using GROUP BY Over ROWNUM expression

WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT MIN(empno) || ' - ' || MAX(empno) "Avail. Emp Numbers"
FROM y
GROUP BY empno-ROWNUM
ORDER BY empno-ROWNUM;

Avail. Emp Numbers
--------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933

#2: Using MATCH_RECOGNIZE (Oracle 12c and up;  credit to Zohar Elkayam)

WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT firstemp || ' - ' || lastemp "Avail. Emp Numbers"
FROM y
MATCH_RECOGNIZE (
  ORDER BY empno
  MEASURES
   A.empno firstemp,
   LAST(empno) lastemp
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B*)
  DEFINE B AS empno = PREV(empno)+1
);

#3: Using LEAD Analytic function (credit to Krishna Jamal)

WITH x AS
(
SELECT empno, LEAD(empno,1) OVER(ORDER BY empno) lead_empno
FROM emp
)
SELECT (empno+1) || ' - ' || (lead_empno-1) "Avail. Emp Numbers"
FROM x
WHERE empno+1!=lead_empno;

Also, see a very similar Puzzle of the Week #15 for more workarounds.

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 Solutions to Puzzle of the Week #15

Puzzle of the Week #15:

Find all the year based intervals from 1975 up to now when the company did not hire employees. Use a single SELECT statement against emp table.

Expected Result:

years
------------
1975 - 1979
1983 - 1986
1988 - 2016

Solutions

#1: Grouping by an expression on ROWNUM (no Analytic functions!)

SQL> col years for a15

SQL> WITH x AS (
  2  SELECT 1975+LEVEL-1 yr
  3  FROM dual
  4  CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
  5  MINUS
  6  SELECT EXTRACT(YEAR FROM hiredate)
  7  FROM emp
  8  )
  9  SELECT MIN(yr) || ' - ' || MAX(yr) "years"
 10  FROM x
 11  GROUP BY yr-ROWNUM
 12  ORDER BY yr-ROWNUM;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

#2: Calculating steps with Analytic function and grouping by a sum of step.

WITH x AS (
SELECT 1975+LEVEL-1 yr
FROM dual
CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
MINUS
SELECT EXTRACT(YEAR FROM hiredate)
FROM emp
), y AS (
SELECT DECODE(yr, LAG(yr,1)OVER(ORDER BY yr)+1, 0, 1) AS step, yr
FROM x
), z AS (
SELECT yr, SUM(step)OVER(ORDER BY yr) grp
FROM y
)
SELECT MIN(yr) || ' - ' || MAX(yr) "years"
FROM z
GROUP BY grp
ORDER BY grp;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

#3: Using Self Outer Join to calculate steps

WITH x AS (
SELECT 1975+LEVEL-1 yr
FROM dual
CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
MINUS
SELECT EXTRACT(YEAR FROM hiredate)
FROM emp
), y AS (
SELECT x1.yr, NVL2(x2.yr, 0, 1) step
FROM x x1 LEFT JOIN x x2 ON x1.yr=x2.yr+1
), z AS (
SELECT yr, SUM(step)OVER(ORDER BY yr) grp
FROM y
)
SELECT MIN(yr) || ' - ' || MAX(yr) "years"
FROM z
GROUP BY grp
ORDER BY grp;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

 

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.

How to mimic MINUS ALL and INTERSECT ALL in Oracle

Some databases, such as PostgreSQL and Teradata, natively support “ALL” keyword with SET operators MINUS (EXCEPT) and INTERSECT. As of current version, Oracle does not support these features; however, a little trick can help emulating the missing functionality.

Let’s see an example. We want to see job titles from department #20 that don’t exist in department #30.

SELECT job
FROM emp
WHERE deptno=20
MINUS
SELECT job
FROM emp
WHERE deptno=30;

--Result:

JOB
---------
ANALYST

If Oracle supported MINUS ALL the result of the following SQL would be quite different:

SELECT job
FROM emp
WHERE deptno=20
MINUS ALL
SELECT job
FROM emp
WHERE deptno=30;

--Result (possible):
JOB
---------
ANALYST
ANALYST
CLERK

The following trick with Analytical function ROW_NUMBER helps with Oracle SQL solution:

SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=20
MINUS
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=30;

JOB               RN
--------- ----------
ANALYST            1
ANALYST            2
CLERK              2

If we ignore (or hide by selecting from the above result) RN column, we would get exactly what we need:

WITH x AS (
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=20
MINUS
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=30
)
SELECT job
FROM x;

JOB
---------
ANALYST
ANALYST
CLERK

The same trick works fine with mimicking INTERSECT ALL statement.
The rule is simple:

SELECT col1, col2, col3, ROW_NUMBER(PARTITION BY col1, col2, col3 ORDER BY 1) rn
FROM 
WHERE ...
INTERSECT
SELECT col1, col2, col3, ROW_NUMBER(PARTITION BY col1, col2, col3 ORDER BY 1) rn
FROM 
WHERE ...

Just make sure to list all selected columns in PARTITION BY clause.

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