MAX() KEEP (DENSE_RANK LAST ORDER BY ) OVER() PARTITION BY()

MAX() KEEP (DENSE_RANK LAST ORDER BY ) OVER() PARTITION BY()

  KEEP DENSE_RANK  

-- Create table 

CREATE TABLE test (name, sal, deptno) AS
SELECT 'a', 1, 1 FROM DUAL
UNION ALL SELECT 'b', 1, 1 FROM DUAL
UNION ALL SELECT 'c', 1, 1 FROM DUAL
UNION ALL SELECT 'd', 2, 1 FROM DUAL
UNION ALL SELECT 'e', 3, 1 FROM DUAL
UNION ALL SELECT 'f', 3, 1 FROM DUAL
UNION ALL SELECT 'g', 4, 2 FROM DUAL
UNION ALL SELECT 'h', 4, 2 FROM DUAL
UNION ALL SELECT 'i', 5, 2 FROM DUAL
UNION ALL SELECT 'j', 5, 2 FROM DUAL;



 select * from test ;

N        SAL     DEPTNO
- ---------- ----------
a          1          1
b          1          1
c          1          1
d          2          1
e          3          1
f          3          1

g          4          2
h          4          2
i          5          2
j          5          2

10 rows selected.





SQL !!



set linesize 300
col min_name_first_sal for a16
col max_name_first_sal for a16
col min_name_last_sa for a16
col max_name_last_sal for a16
col min_name_last_sal for a16
SELECT DISTINCT
deptno,
  MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_sal_first_sal,
  MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal desc ) OVER (PARTITION BY deptno) AS max_sal_first_sal,
  MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_first_sal,
  MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_first_sal,
  MIN(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_last_sal,
  MAX(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_last_sal,
  deptno
FROM test;



   DEPTNO MIN_SAL_FIRST_SAL MAX_SAL_FIRST_SAL MIN_NAME_FIRST_S MAX_NAME_FIRST_S MIN_NAME_LAST_SA MAX_NAME_LAST_SA     DEPTNO
---------- ----------------- ----------------- ---------------- ---------------- ---------------- ---------------- ----------
         2                 4                 5 g                h                i                j                         2
         1                 1                 3 a                c                e                f                         1






 select * from test ;

N        SAL     DEPTNO
- ---------- ----------
a          1          1
b          1          1
c          1          1
d          2          1
e          3          1
f          3          1

g          4          2
h          4          2
i          5          2
j          5          2

10 rows selected.



col least_salary_person for a18

select o.deptno
,min(o.sal) keep  (dense_rank first order by o.sal, o.name) least_salary
,max(o.sal) keep  (dense_rank last order by o.sal , o.name ) MAX_salary
,min(o.name) keep (dense_rank first order by o.sal, o.name ) least_salary_person
 from test o   
group by  o.deptno;




 
    DEPTNO LEAST_SALARY MAX_SALARY LEAST_SALARY_PERSO
---------- ------------ ---------- ------------------
         1            1          3 a
         2            4          5 g

====
select empno,
       deptno,
       sal,
      dense_rank() over (partition by deptno order by sal) as myrank
from   scott.emp;

SQL> SQL>   2    3    4    5
     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          3
      7499         30       1600          4
      7698         30       2850          5

14 rows selected.

SQL> SQL>
select * from (select empno,
       deptno,
       sal,
      dense_rank() over (partition by deptno order by sal ) as myrank
from   scott.emp
)
where 1=1
and MYRANK=4
;
     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7788         20       3000          4
      7902         20       3000          4
      7499         30       1600          4

SQL>