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