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

### 3 thoughts on “3 Solutions to 2018 Oracle SQL Puzzle of the Week #11”

1. surendra beniwal October 8, 2018 / 5:49 pm

why did you use ?
ROWIDTOCHAR(e2.ROWID)

2. surendra beniwal October 8, 2018 / 5:50 pm

is there a post where you explain what you are doing ?

3. Zahar Hilkevich October 9, 2018 / 6:53 am

ROWIDTOCHAR was used just to stress the explicit conversion of ROWID value to a character string. Apparently, implicit conversion was also an option.