참고 링크:

Group By 최대값을 가진 Row를 추출하는 쿼리

[oracle]오라클 최대값의 다른 컬럼 구하기

시작점: GROUP BY절을 사용하여 각 학과별 급여합계와 최고급여, 최저급여를 출력한 상태.

목표: 학과별로 최고급여와 최저급여를 수령하는 교수의 이름을 각각 출력하기.

department table

professor table

SELECT p.deptno, d.dname 학과명, SUM(p.pay+nvl(p.bonus, 0)) 급여합계, MAX(p.pay+nvl(p.bonus, 0)) 최고급여, MIN(p.pay+nvl(p.bonus, 0)) 최저급여
 FROM professor p, department d
 WHERE p.deptno = d.deptno
 GROUP BY p.deptno, d.dname
 ORDER BY p.deptno;

시작시 SQL 쿼리문:

SELECT p.deptno, d.dname 학과명, SUM(p.pay+nvl(p.bonus, 0)) 급여합계, MAX(p.pay+nvl(p.bonus, 0)) 최고급여, MIN(p.pay+nvl(p.bonus, 0)) 최저급여
 FROM professor p, department d
 WHERE p.deptno = d.deptno
 GROUP BY p.deptno, d.dname
 ORDER BY p.deptno;

출력결과

학과별 최고급여, 최저급여와 같은 금액의 급여를 수령하는 교수가 각각 1명씩이라는 가정 하에 진행했습니다.

MAX등의 GROUP BY 표현식으로 최고급여/최저급여를 구하였기 때문에, 해당 값과 같은 급여를 가지는 행(row)의 다른 값(교수 이름 등)을 그냥 가져올 수 없었습니다.

찾아본 결과, 여러 방법이 있었지만, 상기 링크에서 소개한 방식 중 정렬을 통한 최대값과 해당 값을 가진 행(row)의 다른 칼럼의 값을 구하는 방법으로 수정하기로 했습니다.

참조한 SQL 쿼리문:

select
max(text) keep(dense_rank first order by testno desc) as max_text,
max(test_sno) keep(dense_rank first order by testno desc) as max_sno,
max(testno) keep(dense_rank first order by testno desc) as max_no

from test
where
test_sno=1;

출처: https://lee-mandu.tistory.com/35 [개발/일상_Mr.lee]

최종 SQL 쿼리문:

select
 p.deptno, d.dname 학과명, SUM(p.pay+nvl(p.bonus, 0)) 급여합계,
 max(p.name) keep(dense_rank first order by p.pay+nvl(p.bonus, 0) desc) as 최고급여수령자,
 max(p.pay+nvl(p.bonus, 0)) 최고급여,
 --원본 쿼리에서 max를 min으로, 구할 값을 first order by(첫 값) 에서 last order by(끝 값)으로 변경하여 최소값도 구할 수 있었습니다.
 min(p.name) keep(dense_rank first order by p.pay+nvl(p.bonus, 0)) as 최저급여수령자,
 min(p.pay+nvl(p.bonus, 0)) 최저급여
from professor p, department d
where p.deptno = d.deptno
group by p.deptno, d.dname;

실행결과

원본 쿼리에서 max를 min으로, 구할 값을 first order by(첫 값) 에서 last order by(끝 값)으로 변경하여 최솟값도 구할 수 있었습니다.

이 때 구할 값을 그대로 두고, 정렬 방식을 desc에서 변경해도 같은 결과를 얻을 수 있습니다.

사실 지금 사용한 테이블에서는 MAX값과 MIN값이 동일한 순서를 지니므로, 어느쪽을 사용해도 같은 결과를 얻을 수 있었을 것입니다.

 min(p.name) keep(dense_rank first order by p.pay+nvl(p.bonus, 0) asc) as 최저급여수령자,
 min(p.pay+nvl(p.bonus, 0)) 최저급여
--정렬 방식을 명시하지 않으면 asc(오름차순)이 되므로, 편한 방식을 선택하면 됩니다.

출력할 컬럼의 값 keep(dense_rank (첫값, 끝값) order by 정렬할 기준값 정렬방식) 별칭

참고 링크:

Group By 최대값을 가진 Row를 추출하는 쿼리

[oracle]오라클 최대값의 다른 컬럼 구하기