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

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.

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

• Zahar Hilkevich March 12, 2018 / 10:18 pm

no MAX function is ALLOWED in this puzzle

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
)

• Zahar Hilkevich March 14, 2018 / 10:08 pm

MAX Is used here in the subquery

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