2018 Oracle SQL Puzzle of the Week #10

Top Salary Puzzle

Find highest salary in each department without using MAX function

  • Use a single SELECT statement only.
  • For an added complexity (optional): try not using ANY functions at all (neither group, nor analytic, not even scalar)
  • Try to come up with 2-3 different solutions.
  • 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:

DEPTNO MAX_SAL
10 5000
20 3000
30 2850

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

25 thoughts on “2018 Oracle SQL Puzzle of the Week #10

  1. Boobal Ganesan March 12, 2018 / 9:06 pm

    SELECT
    deptno,
    regexp_substr(
    LISTAGG(sal,',') WITHIN GROUP(
    ORDER BY
    sal DESC
    ),'[^,]+',1,1) max_sal
    FROM
    emp
    GROUP BY
    deptno;

  2. Boobal Ganesan March 12, 2018 / 9:11 pm


    SELECT
    distinct deptno,
    sal max_sal
    FROM
    emp
    WHERE
    ( deptno,
    sal ) NOT IN (
    SELECT
    e1.deptno,
    e1.sal
    FROM
    emp e1,
    emp e2
    WHERE
    e1.sal < e2.sal
    AND e1.deptno = e2.deptno
    );

  3. Mahantesh Hiremath March 12, 2018 / 9:12 pm

    SELECT DEPTNO,MAX(SAL) MAX_SAL FROM SCOTT.EMP
    GROUP BY DEPTNO
    ORDER BY 1

  4. suman March 12, 2018 / 9:12 pm

    select * from
    (
    select deptno,sal,row_number() over (partition by deptno order by sal desc) max_sal
    from emp
    )
    where max_sal = 1

  5. Boobal Ganesan March 12, 2018 / 9:19 pm


    SELECT
    distinct
    e1.deptno,
    e1.sal max_sal
    FROM
    emp e1,
    emp e2
    WHERE
    e1.sal < e2.sal (+)
    AND e1.deptno = e2.deptno (+)
    and e2.sal is null;

  6. Boobal Ganesan March 12, 2018 / 9:29 pm


    SELECT
    distinct e1.deptno,e1.sal max_sal
    FROM
    emp e1
    WHERE
    1 = (
    SELECT
    COUNT(distinct e2.sal)
    FROM
    emp e2
    WHERE
    e2.sal >= e1.sal
    AND e2.deptno = e1.deptno);

  7. Mahantesh Hiremath March 12, 2018 / 9:49 pm

    WITH DEPT AS (
    SELECT DISTINCT DEPTNO, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS SalaryRank, SAL
    FROM SCOTT.EMP
    ORDER BY 1
    )
    SELECT DEPTNO, SAL MAX_SAL FROM DEPT WHERE SalaryRank=1

  8. Mahantesh Hiremath March 12, 2018 / 9:57 pm

    select distinct e.DEPTNO ,
    ( select max(sal)
    from scott.emp
    where deptno = e.deptno ) as max_sal
    from scott.emp e
    order by 1;

  9. Mahantesh Hiremath March 12, 2018 / 10:12 pm

    SELECT DEPTNO,MAX(SAL) AS MAX_SAL FROM SCOTT.EMP GROUP BY DEPTNO
    ORDER BY 1

  10. Ranga Reddy K March 13, 2018 / 12:07 am

    SELECT E1.*
    FROM(SELECT SAL,, ROW_NUMBET() OVET(PARTITION BY DEPTNO ORDER BY SAL DESC, EMPNO) Rank)
    WHERE Rank=1

  11. Ranga Reddy K March 13, 2018 / 12:11 am

    SELECT SAL
    FROM EMP
    WHERE SAL IN(SELECT MAX(SAL)
    FROM EMP
    WHERE LEVEL IN(1,2,3)
    CONNECT BY PRIOR SAL>SAL
    GROUP BY LEVEL
    )

  12. Ranga Reddy K March 13, 2018 / 12:24 am

    SELECT E2.*
    FROM(
    SELECT ROWNUM RN, E1.*
    FROM(SELECT SAL, DEPTNO
    FROM EMP
    GROUP BY SAL, DEPTNO
    ORDER BY SAL DESC) E1)E2
    WHERE E2.RN IN(1,2,3)

    • Zahar Hilkevich March 19, 2018 / 8:31 pm

      the result is not what is expected, deptno 30 is missing

  13. Boobal Ganesan March 13, 2018 / 4:27 am

    SELECT
    deptno,
    abs(MIN(-sal) ) max_sal
    FROM
    emp
    GROUP BY
    deptno
    ORDER BY
    deptno;

    MAX function is not used 😉

  14. Shashidhar Reddy March 14, 2018 / 5:04 am

    with temp
    as
    (
    select deptno, sal,
    (select count(e.sal)from emp e where e.deptno=emp.deptno and e.sal>emp.sal) as cnt
    from emp
    )
    select distinct deptno, sal
    from temp
    where cnt=0

  15. KATAYAMA NAOTO March 14, 2018 / 9:18 am

    SELECT DEPTNO,SAL FROM EMP
    MATCH_RECOGNIZE (
    PARTITION BY DEPTNO
    ORDER BY SAL DESC
    ALL ROWS PER MATCH
    PATTERN (ISNULL)
    DEFINE ISNULL AS PREV(ISNULL.SAL) IS NULL)

  16. pradeep March 19, 2018 / 8:18 am

    posted query not getting displayes

  17. pradeep March 19, 2018 / 8:18 am

    displayed

  18. pradeep March 19, 2018 / 8:19 am

    SELECT distinct DEPTNO,sal
    FROM
    ( SELECT dense_rank()over(partition BY deptno order by sal DESC) AS dk,
    DEPTNO DEPTNO,
    SAL
    FROM emp
    )
    WHERE dk=1

  19. pradeep March 19, 2018 / 8:20 am

    SELECT DISTINCT first_value(sal)over(partition BY deptno order by sal DESC ) as max_sal, DEPTNO
    FROM emp

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