# Mimic ROW_NUMBER function

Write a single SELECT statement that produces the same result as the following one:

```SELECT e.*, ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM scott.emp e```
• Use a single SELECT statement only.
• Analytic functions are NOT allowed
• Any SQL clauses that use PARTITION BY keywords are NOT allowed

Expected Result:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 1
7839 KING PRESIDENT 17-NOV-81 5000 10 2
7934 MILLER CLERK 7782 23-JAN-82 1300 10 3
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 1
7902 FORD ANALYST 7566 03-DEC-81 3000 20 2
7566 JONES MANAGER 7839 02-APR-81 2975 20 3
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 4
7369 SMITH CLERK 7902 17-DEC-80 800 20 5
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 1
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 2
7900 JAMES CLERK 7698 03-DEC-81 950 30 3
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 4
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 5
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 6

# Solutions:

### Solution #1. Using MATCH_RECOGNIZE clause

Credit to: Naoto Katayama

``````SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,rn
FROM scott.emp
MATCH_RECOGNIZE (
ORDER BY deptno,ename,empno
MEASURES RUNNING COUNT(*) AS rn
ALL ROWS PER MATCH
PATTERN (FIRSTROW NEXTROWS*)
DEFINE
FIRSTROW AS PREV(FIRSTROW.deptno) IS NULL
OR PREV(FIRSTROW.deptno) != FIRSTROW.deptno,
NEXTROWS AS PREV(NEXTROWS.deptno) = NEXTROWS.deptno
)``````

### Solution #2. Using Self-Join with Cartesian Product and GROUP BY

Partial Credit to: Boobal Ganesan

``````SELECT e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.comm,e1.deptno,
COUNT(*) rn
FROM scott.emp e1 LEFT OUTER JOIN scott.emp e2
ON e1.deptno = e2.deptno
AND e2.ename || ROWIDTOCHAR(e2.ROWID) <= e1.ename || ROWIDTOCHAR(e1.ROWID)
GROUP BY e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.comm,e1.deptno
ORDER BY e1.deptno, COUNT(*)``````

### Solution #3. Using CTE, ROWNUM, and arithmetic formula

``````WITH x AS (
SELECT *
FROM scott.emp
ORDER BY deptno, ename
), y AS (
SELECT deptno, MIN(ROWNUM) min_rn
FROM x
GROUP BY deptno
)
SELECT x.*, ROWNUM-y.min_rn+1 AS rn
FROM x JOIN y ON x.deptno=y.deptno
ORDER BY x.deptno, x.ename``````

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

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.

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

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.

## 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
(
FROM emp
)
SELECT (empno+1) || ' - ' || (lead_empno-1) "Avail. Emp Numbers"
FROM x

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

# Puzzle of the Week #11

Produce the Employee Roll Report that satisfies the following list of requirements:

• Use single SELECT statement
• Single column “Names” should have a list of the employee names separated by comma
• The maximum size of the values in the “Names” column should be 23
• The report should have as few rows as possible
• All the employee names should be concatenated in the alphabetical order

### Expected Result:

(the Length column is added for length verification only)

```Names                                        Length
---------------------------------------- ----------
FORD,JAMES,JONES,KING                            21
MARTIN,MILLER,SCOTT                              19
SMITH,TURNER,WARD                                17```

### Solutions

#1 – Using Recursive WITH clause (Common Table Expression) – to contactenate names

```WITH e AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn, 23 AS maxlen
FROM emp
), x (rn, txt, grp) AS (
SELECT 1, CAST(ename AS VARCHAR2(100)), 1
FROM e
WHERE rn=1
UNION ALL
SELECT e.rn,
CASE WHEN LENGTH(x.txt||','||e.ename)>e.maxlen THEN e.ename
ELSE x.txt||','||e.ename
END,
CASE WHEN LENGTH(x.txt||','||e.ename)>e.maxlen THEN x.grp+1
ELSE x.grp
END
FROM e JOIN x ON e.rn=x.rn+1
)
SELECT MAX(txt) "Names", LENGTH(MAX(txt)) "Length"
FROM x
GROUP BY grp
ORDER BY grp;

Names                         Length
------------------------- ----------
FORD,JAMES,JONES,KING             21
MARTIN,MILLER,SCOTT               19
SMITH,TURNER,WARD                 17

```

#2 – Using Recursive WITH clause (Common Table Expression) – to group names AND LISTAGG function

```WITH t (ename, len, rn) AS (
SELECT ename, LENGTH(ename) + 1, ROW_NUMBER() OVER(ORDER BY ename)
FROM emp
), r (ename, running_len, rn, gp) AS (
SELECT ename, len, rn, 1
FROM t
WHERE rn = 1
UNION ALL
SELECT t.ename,
CASE WHEN t.len > 24 - r.running_len THEN t.len ELSE r.running_len + t.len END,
t.rn,
r.gp + CASE WHEN t.len > 24 - r.running_len THEN 1 ELSE 0 END
FROM t JOIN r ON t.rn = r.rn + 1
)
SELECT LISTAGG(ename, ',') WITHIN GROUP(ORDER BY rn) AS "Names", MAX(running_len) - 1 AS "Length"
FROM r
GROUP BY gp
ORDER BY gp
/
```

#3: Using Recursive WITH clause (CTE) – to group names in a different way

```WITH data (ename, grp, pass) AS (
SELECT ename,
CASE WHEN SUM(LENGTH(ename) + 1) OVER(ORDER BY  ename) - 1 <= 23
THEN 1
ELSE 0
END, 1
FROM emp
UNION ALL
SELECT ename,
CASE WHEN SUM(LENGTH(ename) + 1) OVER (ORDER BY  ename) - 1 <= 23
THEN 1
END, pass + 1
FROM data
WHERE (grp = 0 AND pass = 1) OR grp IS NULL
), x AS (
SELECT LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS names, pass
FROM data
WHERE grp = 1
GROUP BY pass
)
SELECT names "Names", LENGTH(names) AS "Length"
FROM x
ORDER BY 1;```

#4: Using XMLAGG with Regular Expressions

```WITH t AS (
SELECT TRIM(',' FROM XMLAGG(xmlelement(e, ename||',') ORDER BY ename).EXTRACT('//text()')) AS txt
FROM  emp
), x AS (
SELECT LEVEL AS l,
TRIM(',' FROM TRIM(REGEXP_SUBSTR(txt,'.{1,23}(,|\$)',1,LEVEL))) AS names
FROM t
CONNECT BY TRIM(',' FROM TRIM(REGEXP_SUBSTR(txt,'.{1,23}(,|\$)',1,LEVEL))) IS NOT NULL
)
SELECT names "Names", LENGTH(names) "Length"
FROM x
/
```

#5: Using LISTAGG with Regular Expressions

```WITH  x AS (
SELECT LISTAGG (ename, ',') WITHIN GROUP (ORDER BY 1) str
FROM emp
)
SELECT RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',')  "Names",
LENGTH(RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',')) "Length"
FROM x
CONNECT BY RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',') IS NOT NULL```

#6: Using MODEL clause for grouping names

```WITH m AS (
SELECT i, ename, grp, len, prevlen
FROM emp
MODEL
DIMENSION BY (ROW_number() OVER (ORDER BY  ename) AS i)
MEASURES
(
ename AS ename,
CAST('' AS VARCHAR2(24)) AS names,
0 AS grp,
0 AS len,
0 AS prevlen
)
RULES
(
len[i] = LENGTH(ename[CV()]),
prevlen[i] = CASE WHEN (CASE WHEN NVL(prevlen[CV()-1],0) = 0 THEN NVL(len[CV()-1],0)
ELSE NVL(prevlen[CV()-1],0) + 1 +  NVL(len[CV()-1],0)
END) > 23
THEN NVL(len[CV()-1],0)
ELSE CASE WHEN NVL(prevlen[CV()-1],0) = 0 THEN NVL(len[CV()-1],0)
ELSE NVL(prevlen[CV()-1],0) + 1 +  NVL(len[CV()-1],0)
END
END,
grp[i] = NVL(grp[CV()-1],0) + CASE WHEN prevlen[CV()+1] < prevlen[CV()] THEN 1 ELSE 0 END
)
)
SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) AS "Names" , LENGTH(listagg(ename,',') WITHIN GROUP (ORDER BY  ename)) AS "Length"
FROM m
GROUP BY grp;

```

#7: Oracle 12c Solution – Using MATCH_RECOGNIZE clause

```SELECT  LISTAGG(name,',') WITHIN GROUP(ORDER BY name) "Names",
LENGTH(LISTAGG(name,',') WITHIN GROUP(ORDER BY name)) "Length"
FROM  EMP
MATCH_RECOGNIZE
(
ORDER BY ENAME
MEASURES
MATCH_NUMBER() rn,
UP.ENAME name
ALL ROWS PER MATCH
PATTERN (UP+)
DEFINE
UP AS SUM(LENGTH(UP.ENAME || ',')) <= 24
)
GROUP BY RN
ORDER BY RN

```

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