[Mysql] group by 시 가장 큰 값을 가진 row 조회하기

DB 쿼리문을 작성 할 때, 특정 컬럼의 최대 값, 최소 값을 구해야할 경우가 있습니다.
최대 값, 최소 값을 구하기는 쉽지만, 최대, 최소 값을 가진 Row를 조회해야하는 경우 원하는 결과가 안나올 수 있습니다.
이때 사용할 수 있는 몇가지 방법을 알려드리겠습니다.
(더 좋은 방법이 있다면 댓글로 남겨주세요!)

테이블 생성 및 데이터 삽입 쿼리입니다.

CREATE TABLE `employee` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NULL DEFAULT NULL,
	`salary` INT(11) NULL DEFAULT NULL,
	`dept_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `fk_department_id` (`dept_id`),
	CONSTRAINT `fk_department_id` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)
)

CREATE TABLE `department` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)

INSERT INTO `department` (`id`, `name`) VALUES (1, '사업부');
INSERT INTO `department` (`id`, `name`) VALUES (2, '개발부');
INSERT INTO `department` (`id`, `name`) VALUES (3, '영업부');

INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (6, 'hong', 5500, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (3, 'lee', 7000, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (5, 'jang', 4000, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (8, 'lim', 3400, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (7, 'sin', 2800, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (1, 'kim', 3000, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (4, 'go', 3400, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (2, 'park', 2600, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (9, 'chang', 4400, 3);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`) VALUES (10, 'ho', 3600, 3);

 

1. employee 테이블에서 최고 연봉을 받는 Row 조회하기

1) order by 사용하기

select 
  b.name,
  a.name, 
  a.salary
from employee a
join department b on (b.id = a.dept_id)
ORDER BY a.salary desc
limit 1;

#결과
#사업부 lee 7000

연봉을 내림차순하고 한개만 조회하도록하여 연봉이 가장 높은 row를 조회하는 방법입니다. 
최고 연봉을 가진 사람이 여러명이어도 한명만 조회됩니다. (정렬 조건에 따라서 출력되는 row가 달라집니다.)

 

2) where 절에 서브쿼리 사용하기

select 
  b.name,
  a.name, 
  a.salary
from employee a
join department b on (b.id = a.dept_id)
WHERE a.salary = (SELECT MAX(a.salary) from employee a)

#결과
#사업부 lee 7000

where절에 서브쿼리를 이용하는 방법입니다. 서브쿼리 구문을 통해 최고 연봉 값을 구하고 해당 값과 같은 연봉을 가진 row를 조회합니다. 최고 연봉가진 사람이 두명이라면 두개의 행이 출력됩니다. 

 

2. 부서 별로 가장 높은 연봉을 받는 Row 조회하기

select 
  b.name,
  a.name, 
  a.salary
from employee a
join department b on (b.id = a.dept_id)
join (
	select max(salary) as 'max_salary'
	from employee
	group by dept_id
) c on (a.salary = c.max_salary);

#결과
#사업부 lee 7000
#개발부 go 3400
#개발부 lim 3400
#영업부 chang 4400

from 절에 서브쿼리를 사용해서 부서별로 가장높은 연봉을 가진 inline view를 생성하고 최고 연봉을 기준으로 조인하는 방법입니다.