List all employees in 2 columns based on the salary ranking.

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.

Advertisements

2 thoughts on “List all employees in 2 columns based on the salary ranking.

  1. Surya Prakash Mishra January 4, 2016 / 1:12 am

    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.

  2. Zahar Hilkevich January 6, 2016 / 5:07 pm

    OK, It will take me some time, but I promise to update the post and include more detailed explanation.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s