[프로그래머스/SQL] 즐겨찾기가 가장 많은 식당 정보 출력하기

output

sol 1. inner join 사용하는 방법
*left join과는 결과가 달라짐.

select a.food_type,a.rest_id,a.rest_name,a.favorites
from rest_info a
inner join 
(SELECT food_type, max(favorites) favorites
from rest_info 
group by food_type
order by favorites desc) b on a.food_type =b.food_type and a.favorites=b.favorites
order by food_type desc

sol 2. where절 사용하는 방법(1)

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE,FAVORITES) 
IN 
(
    SELECT FOOD_TYPE, MAX(FAVORITES) 
    FROM REST_INFO
    GROUP BY FOOD_TYPE
)
ORDER BY FOOD_TYPE DESC;

sol 3. where절 사용하는 방법(2)

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO A
WHERE (FOOD_TYPE,FAVORITES) 
IN 
(
    SELECT FOOD_TYPE, MAX(FAVORITES) 
    FROM REST_INFO B
    WHERE A.FOOD_TYPE = B.FOOD_TYPE 
)
ORDER BY FOOD_TYPE DESC;