2018 Oracle SQL Puzzle of the Week #12

Top and Bottom Paid Employees

List top and bottom paid employees in each department without using UNION [ALL] operator

  • Use a single SELECT statement only.
  • SET Operators are not allowed
  • 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 DEPTNO SAL
KING 10 5000
MILLER 10 1300
SCOTT 20 3000
FORD 20 3000
SMITH 20 800
BLAKE 30 2850
JAMES 30 950

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 #12

  1. KATAYAMA NAOTO March 26, 2018 / 5:19 pm

    SELECT ENAME,DEPTNO,SAL
    FROM (
    SELECT EMP.*
    ,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) T
    ,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) B
    FROM EMP)
    WHERE T=1 OR B=1
    ORDER BY DEPTNO, T DESC;

    • KATAYAMA NAOTO March 26, 2018 / 5:32 pm

      what is SET Operators ?

      • Zahar Hilkevich March 27, 2018 / 9:52 am

        UNION [ALL], MINUS and INTERSECT are called SET operators

  2. Boobal Ganesan March 26, 2018 / 10:53 pm

    WITH t1 AS (
    SELECT
    deptno,
    sal
    FROM
    (
    SELECT
    deptno,
    MAX(sal) max_sal,
    MIN(sal) min_sal
    FROM
    emp
    GROUP BY
    deptno
    ) UNPIVOT ( sal
    FOR i
    IN ( "MAX_SAL",
    "MIN_SAL" ) )
    ) SELECT
    emp.ename,
    emp.deptno,
    emp.sal
    FROM
    emp,
    t1
    WHERE
    emp.deptno = t1.deptno
    AND emp.sal = t1.sal
    ORDER BY
    deptno,
    sal DESC;

  3. Boobal Ganesan March 26, 2018 / 11:00 pm

    WITH t1 AS (
    SELECT DISTINCT
    deptno,
    sal
    FROM
    emp
    ),t2 AS (
    SELECT
    deptno,
    LISTAGG(sal,
    ‘,’) WITHIN GROUP(
    ORDER BY
    sal DESC
    ) sal
    FROM
    emp
    GROUP BY
    deptno
    ),t3 AS (
    SELECT
    deptno,
    regexp_substr(sal,'[^,]+’,1,1) max_sal,
    regexp_substr(sal,'[^,]+’,1,regexp_count(sal,’,’) + 1) min_sal
    FROM
    t2
    ),t4 AS (
    SELECT
    *
    FROM
    t3 UNPIVOT ( sal
    FOR i
    IN ( “MAX_SAL”,
    “MIN_SAL” ) )
    ) SELECT
    emp.ename,
    emp.deptno,
    emp.sal
    FROM
    t4,
    emp
    WHERE
    t4.deptno = emp.deptno
    AND t4.sal = emp.sal
    ORDER BY
    emp.deptno,
    emp.sal DESC;

  4. Boobal Ganesan March 26, 2018 / 11:14 pm

    WITH t1 AS (
    SELECT
    deptno,
    MAX(sal) max_sal
    FROM
    emp
    GROUP BY
    deptno
    ),t2 AS (
    SELECT
    deptno,
    MIN(sal) min_sal
    FROM
    emp
    GROUP BY
    deptno
    ),t3 AS (
    SELECT
    t1.deptno,
    t1.max_sal,
    t2.min_sal
    FROM
    t1,
    t2
    WHERE
    t1.deptno = t2.deptno
    ),
    t4 as (SELECT
    *
    FROM
    t3 unpivot ( sal for i in ("MAX_SAL","MIN_SAL")))
    select emp.ename,emp.deptno,emp.sal from t4,emp
    where t4.deptno=emp.deptno
    and t4.sal=emp.sal
    order by deptno,sal desc;

  5. Boobal Ganesan March 26, 2018 / 11:27 pm

    SELECT
    t1.ename,
    t1.deptno,
    t1.sal
    FROM
    emp t1
    WHERE
    t1.sal IN (
    SELECT
    MAX(t2.sal)
    FROM
    emp t2
    WHERE
    t1.deptno = t2.deptno
    )
    OR t1.sal IN (
    SELECT
    MIN(t2.sal)
    FROM
    emp t2
    WHERE
    t1.deptno = t2.deptno
    )
    order by deptno,sal desc,ename

  6. Mahantesh Hiremath March 26, 2018 / 11:37 pm

    SELECT ENAME,DEPTNO,SAL FROM
    (
    SELECT ENAME,DEPTNO,SAL,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) T FROM
    (
    SELECT EMP.ENAME,EMP.DEPTNO,EMP.SAL

    FROM scott.emp EMP ) A

    WHERE
    (DEPTNO,SAL) IN (
    SELECT DEPTNO,MAX(SAL)
    FROM scott.emp
    GROUP BY DEPTNO)
    OR
    (DEPTNO,SAL) IN (
    SELECT DEPTNO,MIN(SAL)
    FROM scott.emp
    GROUP BY DEPTNO)
    ) mm
    ORDER BY mm.DEPTNO, mm.T

  7. Boobal Ganesan March 26, 2018 / 11:40 pm

    WITH t1 AS (
    SELECT
    t1.ename,
    t1.deptno,
    t1.sal,
    CASE
    WHEN t1.sal IN (
    SELECT
    MAX(t2.sal)
    FROM
    emp t2
    WHERE
    t1.deptno = t2.deptno
    ) THEN 1
    WHEN t1.sal IN (
    SELECT
    MIN(t2.sal)
    FROM
    emp t2
    WHERE
    t1.deptno = t2.deptno
    ) THEN 2
    END
    sal_rnk
    FROM
    emp t1
    ) SELECT
    ename,
    deptno,
    sal
    FROM
    t1
    WHERE
    sal_rnk IN (
    1,
    2
    )
    ORDER BY
    deptno,
    sal DESC,
    ename;

  8. Boobal Ganesan March 26, 2018 / 11:50 pm

    WITH t1 AS (
    SELECT
    deptno,
    MAX(sal) max_sal,
    MIN(sal) min_sal
    FROM
    emp
    GROUP BY
    deptno
    ) SELECT
    emp.ename,
    emp.deptno,
    emp.sal
    FROM
    t1,
    emp
    WHERE
    t1.deptno = emp.deptno
    AND emp.sal IN (
    t1.max_sal,
    t1.min_sal
    )
    ORDER BY
    emp.deptno,
    emp.sal DESC,
    emp.ename;

  9. KATAYAMA NAOTO March 30, 2018 / 7:33 pm

    SELECT ENAME, DEPTNO, SAL FROM EMP
    MODEL RETURN UPDATED ROWS
    PARTITION BY(DEPTNO)
    DIMENSION BY(
    EMPNO
    ,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) T
    ,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) B)
    MEASURES(ENAME,SAL,0 DUMMY)
    RULES(DUMMY[ANY,1,ANY]ORDER BY EMPNO = 1,DUMMY[ANY,ANY,1]ORDER BY EMPNO = 1)
    ORDER BY DEPTNO,SAL DESC,EMPNO;

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