[SQL문제풀기] 식품분류별 가장 비싼 식품의 정보 조회하기

문제

MYSQL, ORACLE

내가 작성한 정답

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

다른 사람들이 작성한 정답

select category, price, product_name 
from food_product t1
where price = (
    select max(price) 
    from food_product 
    where t1.category = category)
and category in ('과자', '국', '김치', '식용유')
order by price desc

SELECT A.CATEGORY, A.PRICE AS MAX_PRICE, A.PRODUCT_NAME 
FROM (
    SELECT FP.CATEGORY, FP.PRICE, FP.PRODUCT_NAME, ROW_NUMBER() OVER (PARTITION BY FP.CATEGORY 
    ORDER BY FP.PRICE DESC) AS IDX 
    FROM FOOD_PRODUCT FP
    WHERE FP.CATEGORY IN ('과자', '국', '김치', '식용유')
) A
WHERE IDX = 1
ORDER BY A.PRICE DESC;