https://www.hackerrank.com/challenges/earnings-of-employees/problem?isFullScreen=true
Top Earners | HackerRank
Find the maximum amount of money earned by any employee, as well as the number of top earners (people who have earned this amount).
www.hackerrank.com
코드설명
GROUP BY + ROWNUM 을 활용합니다.
ORACLE 코드
정답코드1입니다.
SELECT *
FROM (
SELECT MONTHS * SALARY, COUNT(EMPLOYEE_ID)
FROM EMPLOYEE
GROUP BY (MONTHS * SALARY)
ORDER BY MONTHS * SALARY DESC
)
WHERE ROWNUM = 1;
오답코드1입니다. MAX 함수의 MAX_SALARY를 쿼리에 사용하려고 했지만, not a single-group group function 에러가 발생합니다.
SELECT MAX(MONTHS * SALARY),
( SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEE WHERE MONTHS*SALARY = (SELECT MAX(MONTHS * SALARY) FROM EMPLOYEE) )
FROM EMPLOYEE;