3 Solutions to 2018 Oracle SQL Puzzle of the Week #11

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s