[MySQL] group by에서 최대값을 가진 row들을 가져오기

테이블에서 최대값을 구하기 위해서는 집계함수를 써야 하는데  이 때 집계함수는 select문에서 사용 가능하다. 

최대값만 가져오는 건 쉽지만 최대값을 가진 row를 불러오는 것은 또 다른 문제이며 group by에서 여러개의 그룹에서 각 column에

해당하는 최대값 row들을 불러오는 것은 또 다른 문제이다. 쉬운 것부터 확인해보자

 

 

1. 특정 column에서 최대값을 가진 table의 row 가져오기

다음과 같은 테이블이 있을 때 가장 비싼 가격을 가진 물건을 가진 id와 물건의 이름을 가진 row를 가지고 오고 싶다면 어떻게 해야할까?

1. 서브쿼리를 사용하던지

2. 가격을 내림차순으로 정렬한 후 첫번째 row를 가지고 오면된다.

# 1번 방법
select *
from post1 
where price = 
(select max(price) from post1);
# 2번 방법
select *
from post1
order by price desc limit 1;

 

 

2. 여러개의 group에서 각각 최대값을 가진 row를 가져오기

 

우선 group by를 사용하기전 group by의 결과값이 어떻게 형성되는지 알고 있어야 한다.

group by id와 같이 작성한다면 서로 다른 id를 가진 레코드끼리 뭉쳐 table을 형성한다고 보면 된다. 

그 때 table의 개수는 서로 다른 id의 가짓수이다. id가 1,1,2,2,2,3,3,3,3,3 인 테이블이 있으면 id는 1,2,3 인 group이 생기는 것이다.

이 때 group by의 결과값을 이용하기 위해서는 select 문에서 group by의 결과값을 가지고 있는 table의 갯수와 같은 column을 지정해주어야 한다.

위의 table의 경우에 id별로 가장 비싼 물건을 가져오고 싶다면 어떻게 해야할까? 우선 id별로 group by를 시킨 후에 거기서 max값을 가져와야 한다.

 

select id,max(price)
from post1
group by id;

이와 같이 작성시 아래와 같은 테이블을 반환한다. 

이 때 where절의 서브쿼리를 이용하면 최대값을 가진 행을 반환할 것 같지만 그렇지 않다.

where절에서 서브 쿼리는 연산자에 따라 가질 수 있는 row의 갯수가 정해져있으며 column은 무조건 하나만 가지고 있어야 한다. 

where절에서 서브쿼리를 상수 비교용으로 사용하기 위해서는 여러개의 column을 가질 수 없고 하나의 row만 가질 수 있다.

단 in과 같은 연산자일 경우 여러개의 row를 가질 수 있다. 즉 연산자가 무엇이느냐에 따라 서브쿼리의 반환값을 잘 지정해야 한다. 

 

본론으로 돌아와 결론을 말하자면 위와 같은 최대값을 가지고 있는 table에서 auto_increment와 같이 고유의 id값이 있다면 더 쉽게 구할 수 있지만 현재로써는 연결고리가 없는 상황이다. 따라서 다음과 같이 작성한다.

id와 각 그룹의 최대값을 가지고 잇는 row 3개를 가지는 서브쿼리와 구하고자 하는table을 join한다. 그 때 가격과 속한 id가 같다면

같은 row라고 볼 수 있으므로 where절에 p.id = j.id and p.price = j.price와 같이 적어준다. 가격만 비교하게 되면 다른 그룹의

같은 가격을 가진 row까지 추가될 수 있기 때문에 꼭 id와 최대값을 조건으로 같이 걸어줘야 한다.