[SQL] 프로그래머스 식품분류별 가장 비싼 식품의 정보 조회하기

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

난이도

Level 4

문제

코드

MySQL

SELECT P.CATEGORY, S.MAX_PRICE, P.PRODUCT_NAME
FROM FOOD_PRODUCT P
JOIN (SELECT MAX(PRICE) MAX_PRICE, CATEGORY
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY) S
ON P.CATEGORY = S.CATEGORY
AND P.PRICE = S.MAX_PRICE
ORDER BY 2 DESC;

Oracle

SELECT P.CATEGORY, M.MAX_PRICE, P.PRODUCT_NAME
FROM FOOD_PRODUCT P JOIN (
    SELECT CATEGORY, MAX(PRICE) MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY
) M
ON P.CATEGORY = M.CATEGORY
AND P.PRICE = M.MAX_PRICE
ORDER BY 2 DESC;