[sql] 조건에 맞는 사원 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284527

initial

select max(t.score) as SCORE, t.EMP_NAME,t.POSITION, t.EMAIL
from(
select a.EMP_NO, a.EMP_NAME,a.POSITION, a.EMAIL, sum(c.SCORE) as score
from HR_EMPLOYEES a
join HR_DEPARTMENT b
on a.DEPT_ID = b.DEPT_ID
join HR_GRADE c
on a.EMP_NO = c.EMP_NO
group by a.EMP_NO, a.EMP_NAME,a.POSITION, a.EMAIL
    ) t
group by t.EMP_NAME,t.POSITION, t.EMAIL

but prob is groupbing by those 4 attributes doesnt work cuz each employee has unique attributes and so we are gonna get 1 row per employee anyway.

sol

actually since we are having 1 unique row per employee and its sum of scores, we can just order by score descending and limit 1 to get the top result. Its clever trick

SELECT SCORE, EMP_NO, EMP_NAME, POSITION, EMAIL
FROM (SELECT EMP_NO, SUM(SCORE) AS SCORE
      FROM HR_GRADE
      GROUP BY EMP_NO) AS G
JOIN HR_EMPLOYEES E USING(EMP_NO)
ORDER BY SCORE DESC
LIMIT 1