[MySQL/Oracle] max, min 값을 가진 row select 하기

DB 쿼리문을 짤 때 해당 컬럼에서 최댓값을 가져오는 max() 함수나 최솟값을 가져오는 min() 함수는 자주 써봤을 것이다.

하지만 최댓값, 최솟값만이 아니라 그 값을 가지고 있는 row의 다른 컬럼들이 필요할 때가 있다.

다양한 방법으로 해결할 수 있겠지만 내가 생각한 세가지 방법 정도만 설명해보겠다.

기존에 작성한 방법 중 몇가지는 결과값이 하나일 때만 적용되는 방법이라, 내용을 수정하였다.

이전 작성글은 아래 펼치기로 확인 가능하니 참고용으로만 보면 될듯하다.

 

아래와 같은 기본적인 employee 테이블로 예를 들어보겠다.

name	number	salary
김	40	30000
박	23	35000
이	51	25000

 

이 테이블에서 salary가 max, min값인 사람의 이름과 사번을 select 해보자.

 

1) 서브쿼리 이용하기

where절에 조건을 추가하는 방법이다. 가장 생각하기 쉬운 방법인듯.

 - Oracle/MySQL 동일

SELECT * FROM employee WHERE salary = (SELECT max(salary) FROM employee);

 

2) 구문 이용하기

구문이라는 말이 적절한지는 모르겠지만.. dbms에 존재하는 문법을 쓴다는 정도로 이해하면 될듯.

Oracle에만 관련 문법이 존재한다. (MySQL은 서브쿼리 방법을 이용하는 걸로..!)

 - Oracle

오라클에선 max(~) KEEP(DENSE_RANK FIRST ORDER BY ~ DESC) 라는 문법을 이용한다.

위 테이블에서 salary가 max인 사람의 이름은

SELECT max(name) KEEP(DENSE_RANK FIRST ORDER BY salary DESC) FROM employee;

라는 쿼리문을 쓰면 나온다. 사번은 name 자리에 number를 넣으면 잘 출력된다.

반대로 salary가 min인 사람의 이름은

SELECT min(name) KEEP(DENSE_RANK LAST ORDER BY salary DESC) FROM employee;

이렇게 출력한다. 마찬가지로 사번은 name 자리에 number만 넣어주면 된다.

 

간단한 쿼리를 짤때는 서브쿼리 이용하는 방법이 가장 좋을거 같고,

엄청 복잡한 쿼리 중에 max값을 가진 row를 써야할 일이 있다면 오라클의 구문을 활용하는게 가장 보기 좋을 것 같다.

 

1) 인라인 뷰 를 이용하기

salary로 정렬한 후 ROWNUM을 이용해서 첫 row만 가져오는 방법이다.

 

 - Oracle

SELECT * FROM (SELECT * FROM employee ORDER BY salary) WHERE rownum = 1;

 

 - MySQL

SELECT * FROM (SELECT @RNUM:=@RNUM+1 AS ROWNUM, e.name, e.number FROM employee e, (SELECT @RNUM:=0) r ORDER BY salary) t where ROWNUM = 1;

이 방법은 Oracle에서는 몰라도 MySQL에서는 그닥 좋은 방법이 아닌거같다. rownum 하나 쓰자고 select를 몇번 써야되는건지....

 

2) 서브쿼리 이용하기

where절에 조건을 추가하는 방법이다. 가장 생각하기 쉬운 방법인듯.

 

 - Oracle/MySQL 동일

SELECT * FROM employee WHERE salary = (SELECT max(salary) FROM employee);

 

3) 구문 이용하기

구문이라는 말이 적절한지는 모르겠지만.. dbms에 존재하는 문법을 쓴다는 정도로 이해하면 될듯.

사실 오라클에만 관련 문법이 있지만 mysql의 또다른 방법도 그냥 이 범주에 넣었다.

 

 - Oracle

오라클에선 max(~) KEEP(DENSE_RANK FIRST ORDER BY ~ DESC) 라는 문법을 이용한다.

위 테이블에서 salary가 max인 사람의 이름은

SELECT max(name) KEEP(DENSE_RANK FIRST ORDER BY salary DESC) FROM employee;

라는 쿼리문을 쓰면 나온다. 사번은 name 자리에 number를 넣으면 잘 출력된다.

반대로 salary가 min인 사람의 이름은

SELECT min(name) KEEP(DENSE_RANK LAST ORDER BY salary DESC) FROM employee;

이렇게 출력한다. 마찬가지로 사번은 name 자리에 number만 넣어주면 된다.

 

 - MySQL

MySQL은 오라클과는 다르게 따로 제공되는 문법이 없어서 꼼수아닌 꼼수?를 이용해야 한다.

 

from절의 서브쿼리에서 order by 한 다음 제일 첫번째 row만 select를 하면 위와 같은 효과를 낼 수 있다.

 

말로만 들어선 잘 모르겠으니 쿼리를 보자.

 

SELECT name, number

 

 FROM ( SELECT * FROM employee ORDER BY salary DESC LIMIT 1);

 

이렇게 하면 salary가 max인 사람의 name과 number가 출력된다.

 

반대로 salary가 min일 때는

 

SELECT DISTINCT name, number

 

 FROM ( SELECT * FROM employee ORDER BY salary LIMIT 1);

default가 ASC로 되어있기 때문에 DESC를 빼기만 하면 된다.