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.
Hi Zahar,
I would like to say thank for your valuable post.and If possible then please describe the flow of SQL query in depth how it will execute and fetch records from tables.
OK, It will take me some time, but I promise to update the post and include more detailed explanation.