[프로그래머스] SUM, MAX, MIN: 물고기 종류 별 대어 찾기

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

프로그래머스

제출 코드

SELECT INFO.ID AS ID, 
       INFO.FISH_NAME AS FISH_NAME, 
       MAX_INFO.MAX_LENGTH AS LENGTH
FROM
(SELECT N.FISH_TYPE AS FISH_TYPE,
       MAX(I.LENGTH) AS MAX_LENGTH
FROM FISH_INFO AS I , FISH_NAME_INFO AS N
WHERE I.FISH_TYPE = N.FISH_TYPE
GROUP BY N.FISH_TYPE) AS MAX_INFO, 

(SELECT I.ID AS ID,
       I.FISH_TYPE AS FISH_TYPE,
       I.LENGTH AS LENGTH,
       N.FISH_NAME  AS FISH_NAME
FROM FISH_INFO AS I, FISH_NAME_INFO AS N
WHERE I.FISH_TYPE = N.FISH_TYPE) AS INFO

WHERE 
MAX_INFO.FISH_TYPE = INFO.FISH_TYPE
AND MAX_INFO.MAX_LENGTH = INFO.LENGTH