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.

Advertisements

Simulate LEAD and LAG functions using other analytic functions

Puzzle: Simulate LEAD and LAG functions using other analytic functions

Level: Intermediate

Solution:

Let’s say that we need to see every employee’s name and 2 more employees who were hired right before. The expected result may look like this:

ENAME      HIREDATE  EMPL1      EMPL2
---------- --------- ---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81 SMITH
WARD       22-FEB-81 ALLEN      SMITH
JONES      02-APR-81 WARD       ALLEN
BLAKE      01-MAY-81 JONES      WARD
CLARK      09-JUN-81 BLAKE      JONES
TURNER     08-SEP-81 CLARK      BLAKE
MARTIN     28-SEP-81 TURNER     CLARK
KING       17-NOV-81 MARTIN     TURNER
JAMES      03-DEC-81 KING       MARTIN
FORD       03-DEC-81 JAMES      KING
MILLER     23-JAN-82 FORD       JAMES
SCOTT      19-APR-87 MILLER     FORD
ADAMS      23-MAY-87 SCOTT      MILLER

It is a no-brainer task if we employ LAG function:

SELECT ename, hiredate, 
              LAG(ename,1) OVER(ORDER BY hiredate) empl1, 
              LAG(ename,2) OVER(ORDER BY hiredate) empl2
FROM emp;

One of the possible approaches is to use ROWS window attribute with MIN/MAX analytic functions:

SELECT ename, hiredate, 
       MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) empl1,
       MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) empl2
FROM emp;

Common rule is:

LAG(value_expr,offset,default) OVER ([partition_clause] order_by_clause )

is the same as

NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause  
   ROWS BETWEEN offset PRECEDING AND offset PRECEDING), default)

and

LEAD(value_expr,offset,default) OVER ([partition_clause] order_by_clause )

is the same as

NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause  
  ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING), default)

This substitution becomes essential in other RDBMS where MIN/MAX analytic functions are supported while LEAD/LAG are not. Teradata is one of the examples.

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.

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.