# Second Top Employee as of the Start of Employment

List all employees who were 2nd top paid in the entire company as of the time their employment started

• Use a single SELECT statement only.
• At the time of employment start the rank of the employee by salary should be 2.
• Show the top salary at the time when the employee started with the company.
• We assume that no employees have ever been terminated since day 1.
• You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points.
• The scoring rules can be found here.
• Solutions must be submitted as comments to this blog post.
• Use <pre>or <code> html tags around your SQL code for better formatting and to avoid losing parts of your SQL.

Expected Result:

ENAME JOB SAL HIREDATE MAX_SAL
WARD SALESMAN 1250 22-FEB-81 1600
BLAKE MANAGER 2850 01-MAY-81 2975
FORD ANALYST 3000 03-DEC-81 5000
SCOTT ANALYST 3000 19-APR-87 5000

A correct answer (and workarounds!) will be published here in about a week.

If you like this post, you may want to join my 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.

### 11 thoughts on “2018 Oracle SQL Puzzle of the Week #13”

1. Boobal Ganesan April 10, 2018 / 4:27 pm

```WITH t1 AS ( SELECT ename, job, sal, hiredate, CASE WHEN ( SELECT COUNT(e2.sal) FROM emp e2 WHERE e1.sal e2.hiredate ) = 1 THEN 1 ELSE 0 END sal_pos, ( SELECT MAX(e2.sal) FROM emp e2 WHERE e1.sal e2.hiredate ) max_sal FROM emp e1 ) SELECT * FROM t1 WHERE sal_pos = 1;```

2. Zahar Hilkevich April 10, 2018 / 6:52 pm

` or ` tags as it lost some operators, or better - provide a link to livesql.oracle.com script``
3. Mahantesh Hiremath April 11, 2018 / 3:54 am

Query will look like mess!!! but its giving correct result..!!:) Please execute and see the result

SELECT MM.ENAME,MM.JOB,MM.SAL,MM.HIREDATE,MM.MAX_SAL FROM(
SELECT LL.ENAME,LL.JOB,LL.SAL,LL.HIREDATE,LL.E2SAL MAX_SAL,DENSE_RANK() OVER (PARTITION BY E2JOB ORDER BY SAL DESC) SAL_RNK FROM
(
SELECT MM.*, RANK() OVER (PARTITION BY EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ORDER BY E2SAL DESC) RNK FROM
(
SELECT e1.*,e2.HIREDATE e2HIREDATE,e2.ENAME e2ENAME,e2.JOB e2JOB,e2.SAL e2SAL from SCOTT.emp e1, SCOTT.emp e2
where e1.HIREDATE>=e2.HIREDATE AND e1.EMPNOe2.EMPNO AND e1.JOB NOT IN (‘CLERK’) AND e2.JOB NOT IN (‘CLERK’)
ORDER BY e2.HIREDATE ASC
) MM
ORDER BY HIREDATE ASC
) LL
WHERE LL.RNK IN (1)
AND SAL< E2SAL) MM
WHERE SAL_RNK=1
ORDER BY 4 ASC

• Mahantesh Hiremath April 12, 2018 / 9:58 pm

Please consider bellow submitted the wrong query before …:(

SELECT MM.ENAME,MM.JOB,MM.SAL,MM.HIREDATE,MM.MAX_SAL FROM(
SELECT LL.ENAME,LL.JOB,LL.SAL,LL.HIREDATE,LL.E2SAL MAX_SAL,DENSE_RANK() OVER (PARTITION BY E2JOB ORDER BY SAL DESC) SAL_RNK FROM
(
SELECT MM.*, RANK() OVER (PARTITION BY EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ORDER BY E2SAL DESC) RNK FROM
(
SELECT e1.*,e2.HIREDATE e2HIREDATE,e2.ENAME e2ENAME,e2.JOB e2JOB,e2.SAL e2SAL from SCOTT.emp e1, SCOTT.emp e2
where e1.HIREDATE>=e2.HIREDATE AND e1.EMPNOe2.EMPNO AND e1.JOB NOT IN (‘CLERK’) AND e2.JOB NOT IN (‘CLERK’)
ORDER BY e2.HIREDATE ASC
) MM
ORDER BY HIREDATE ASC
) LL
WHERE LL.RNK IN (1)
AND SAL< E2SAL) MM
WHERE SAL_RNK=1
ORDER BY 4 ASC

4. KATAYAMA NAOTO April 11, 2018 / 10:43 pm

SELECT ENAME, JOB, SAL, HIREDATE, MAX_SAL FROM (
SELECT * FROM EMP
MODEL
DIMENSION BY (ROW_NUMBER()OVER(ORDER BY HIREDATE) RN)
MEASURES(ENAME, JOB, SAL, HIREDATE, MAX(SAL)OVER(ORDER BY HIREDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) MAX_SAL, -1 NEXT_SAL)
RULES(
NEXT_SAL[ANY]ORDER BY RN =
CASE
WHEN NEXT_SAL[CV()-1] IS NULL THEN NEXT_SAL[CV()]
WHEN SAL[CV()] = MAX_SAL[CV()] THEN MAX_SAL[CV()-1]
WHEN SAL[CV()] ＜ MAX_SAL[CV()] AND SAL[CV()] ＞ NEXT_SAL[CV()-1] THEN SAL[CV()]
ELSE NEXT_SAL[CV()-1]
END))
WHERE SAL = NEXT_SAL;

5. Zahar Hilkevich April 17, 2018 / 10:06 am

almost there, if two employees get paid the same top salary, then nobody makes the 2nd top and the rank will never be equal to 2 after that date, until someone else makes even more. You mimic DENSE_RANK=2 with the MODEL, not RANK=2