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) I wrote some solutions but I need different than my from you....Look here my solutions...
1)SELECT
deptno,
regexp_substr(
LISTAGG(sal,',') WITHIN GROUP(
ORDER BY
sal DESC
),'[^,]+',1,1) max_sal
FROM
emp
GROUP BY
deptno;
2)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)
SELECT DEPTNO,MAX(SAL) MAX_SAL FROM SCOTT.EMP
GROUP BY DEPTNO
ORDER BY 1
4)
select * from
(
select deptno,sal,row_number() over (partition by deptno order by sal desc) max_sal
from emp
)
where max_sal
5)
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)
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)
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)
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)
SELECT DEPTNO,MAX(SAL) AS MAX_SAL FROM SCOTT.EMP GROUP BY DEPTNO
ORDER BY 1
no MAX function is ALLOWED in this puzzle
SELECT E1.*
FROM(SELECT SAL,, ROW_NUMBET() OVET(PARTITION BY DEPTNO ORDER BY SAL DESC, EMPNO) Rank)
WHERE Rank=1
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
)
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)
SELECT
deptno,
abs(MIN(-sal) ) max_sal
FROM
emp
GROUP BY
deptno
ORDER BY
deptno;
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
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
Note:-Do you have any better solution than from New version like 12c or 18c...Is it possible to do it by applying any 18c version features....Can I expect something different answer from you rather than my answer...even I could used Abs(min(-Sal)) user defined function.
I'm throwing this over to the community for their experimentation, learning, etc.