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.

Advertisements

List all employees in 2 columns based on the salary ranking.

Problem: List all employee names and their respective salaries in 2 columns based in the salary ranking (from the highest to the lowest).

Expected Result:

 ID LEFT_NAME      LEFT_SAL RIGHT_NAME    RIGHT_SAL
--- ------------ ---------- ------------ ----------
  1 KING               5000 FORD               3000
  2 SCOTT              3000 JONES              2975
  3 BLAKE              2850 CLARK              2450
  4 ALLEN              1600 TURNER             1500
  5 MILLER             1300 WARD               1250
  6 MARTIN             1250 ADAMS              1100
  7 JAMES               950 SMITH               800

Problem Level: Intermediate/Advanced

Solution:
I have picked 5 best performing methods to solve this problem. The idea behind each method can be found in my book: “Oracle SQL Tricks and Workarounds”

Method/Workaround #1: Using Hierarchical Query (Level: Advanced)

WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) RN
FROM EMP
)
SELECT  rn/2 AS id, PRIOR ename left_name, PRIOR sal left_sal, ename right_name, sal right_sal
FROM X
WHERE MOD(level,2)=0
START WITH rn=1
CONNECT BY rn=1+PRIOR rn

Method/Workaround #2: Using Analytical Function (Level: Advanced)

WITH X AS (
SELECT ename left_name, sal left_sal, 
       LEAD(ename, 1) OVER(ORDER BY sal DESC) AS right_name, 
       LEAD(sal, 1) OVER(ORDER BY sal DESC) as right_sal,
       ROW_NUMBER() OVER(ORDER BY sal DESC) rn
from emp
)
SELECT (rn+1)/2 AS ID, left_name, left_sal,
                       right_name, right_sal
FROM X
WHERE MOD(rn,2)=1
ORDER BY rn

Method/Workaround #3: Using PIVOT Clause (Level: Advanced)

SELECT *
FROM (SELECT CEIL(rn/2) AS ID, ename, sal, 2-MOD(rn,2) AS col_no
      FROM (SELECT ename, sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn
            FROM emp
            )
      )
PIVOT (MAX(ename) AS name,
       MAX(sal)   AS sal
       FOR (col_no) IN (1 AS left, 2 AS right)
       )
ORDER BY 1

Method/Workaround #4: Using MAX function on concatenated column expression (Level: Advanced)

WITH X AS (
SELECT LPAD(sal, 5, '0') || ename as sname, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT CEIL(rn/2) ID, SUBSTR(MAX(SNAME), 6) left_name,  TO_NUMBER(SUBSTR(MAX(SNAME), 1, 5)) left_sal, 
                      SUBSTR(MIN(SNAME), 6) right_name, TO_NUMBER(SUBSTR(MIN(SNAME), 1, 5)) right_sal
FROM X
GROUP BY CEIL(rn/2)
ORDER BY 1

Method/Workaround #5: Using Self-Join (Level: Intermediate)

WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT B.rn/2 AS ID, a.ename AS left_name, a.sal AS left_sal,
                     b.ename AS right_name, b.sal AS right_sal
FROM x a LEFT JOIN x b ON a.rn+1=b.rn 
WHERE mod(a.rn,2)=1   

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

Substitute a self outer join with Connect By, by Zahar Hilkevich

Self [outer] join is a very common and useful technique in Oracle SQL and in other flavors of SQL.

Let’s consider a trivial task of retrieving manager’s name and number next to each employee’s record (we are using scott schema):

SELECT a.ename, a.empno, a.job, a.deptno,
       b.ename AS manager,
       b.empno AS mgrno
FROM emp a LEFT JOIN emp b ON a.mgr=b.empno
ORDER BY 1

Result:

ENAME           EMPNO JOB           DEPTNO MANAGER         MGRNO
---------- ---------- --------- ---------- ---------- ----------
ADAMS            7876 CLERK             20 SCOTT            7788
ALLEN            7499 SALESMAN          30 BLAKE            7698
BLAKE            7698 MANAGER           30 KING             7839
CLARK            7782 MANAGER           10 KING             7839
FORD             7902 ANALYST           20 JONES            7566
JAMES            7900 CLERK             30 BLAKE            7698
JONES            7566 MANAGER           20 KING             7839
KING             7839 PRESIDENT         10
MARTIN           7654 SALESMAN          30 BLAKE            7698
MILLER           7934 CLERK             10 CLARK            7782
SCOTT            7788 ANALYST           20 JONES            7566
SMITH            7369 CLERK             20 FORD             7902
TURNER           7844 SALESMAN          30 BLAKE            7698
WARD             7521 SALESMAN          30 BLAKE            7698

Execution Plan
----------------------------------------------------------
Plan hash value: 2322654302

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   490 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |    14 |   490 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |      |    14 |   490 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   350 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."MGR"="B"."EMPNO"(+))

Note, that an outer join (left join) was used here to retrieve KING’s record as that employee is a president and does not have a manager.

The following workaround is based on the CONNECT BY clause without the use of START WITH as we need to retrieve all employee records:

SELECT ename, empno, job, deptno,
       MAX(PRIOR ename) AS manager,
       MAX(PRIOR empno) AS mgrno
FROM emp
WHERE LEVEL<=2
CONNECT BY mgr=PRIOR empno
GROUP BY ename, empno, job, deptno
ORDER BY 1

Result:

ENAME           EMPNO JOB           DEPTNO MANAGER         MGRNO
---------- ---------- --------- ---------- ---------- ----------
ADAMS            7876 CLERK             20 SCOTT            7788
ALLEN            7499 SALESMAN          30 BLAKE            7698
BLAKE            7698 MANAGER           30 KING             7839
CLARK            7782 MANAGER           10 KING             7839
FORD             7902 ANALYST           20 JONES            7566
JAMES            7900 CLERK             30 BLAKE            7698
JONES            7566 MANAGER           20 KING             7839
KING             7839 PRESIDENT         10
MARTIN           7654 SALESMAN          30 BLAKE            7698
MILLER           7934 CLERK             10 CLARK            7782
SCOTT            7788 ANALYST           20 JONES            7566
SMITH            7369 CLERK             20 FORD             7902
TURNER           7844 SALESMAN          30 BLAKE            7698
WARD             7521 SALESMAN          30 BLAKE            7698

Execution Plan
----------------------------------------------------------
Plan hash value: 2826654915

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    14 |   350 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY                 |      |    14 |   350 |     4  (25)| 00:00:01 |
|*  2 |   FILTER                       |      |       |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     TABLE ACCESS FULL          | EMP  |    14 |   350 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=2)
   3 - access("MGR"=PRIOR "EMPNO")

This execution plan looks somewhat better than the previous one, mainly, because we only used a single copy of the emp table while the first query used two.

P.S. If you like this trick, you can find many more in my book “Oracle SQL Tricks and Workarounds”