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>
