2018 Oracle SQL Puzzle of the Week #13

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.

My Oracle Group on Facebook:

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.

Advertisements

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

    Please re-post your solution within

     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

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