[Programmers] 물고기 종류 별 대어 찾기 - 293261

GROUP BY 될 것 같은데 자꾸 오류 대잔치로 돌고 돌아 정답을 맞춘 문제..

웨지감자; 여전히 의문.. 이유 알아내면 적어 놓겠슈

 

Question

 

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.

물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요.
결과는 물고기의 ID에 대해 오름차순 정렬해주세요.
단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

 

 

Point

 

• WHERE 절 sub query를 사용. ( <- 없이 GROUP BY를 하게 되면 에러 발생)
• sub query에서 MAX 값 추출 -> 길이 비교.

 

Code (After)

 

WITH FISH_INFO_MAX AS(
    SELECT ID, FISH_NAME, LENGTH
            , MAX(LENGTH) OVER(PARTITION BY I.FISH_TYPE) AS MAX_LENGTH
    FROM FISH_INFO I
    LEFT JOIN FISH_NAME_INFO N ON I.FISH_TYPE = N.FISH_TYPE
)
SELECT ID, FISH_NAME, LENGTH
FROM FISH_INFO_MAX
WHERE LENGTH = MAX_LENGTH
ORDER BY 1

 

Code (Before)

 

SELECT ID, FISH_NAME, LENGTH
    FROM FISH_INFO I
    JOIN FISH_NAME_INFO N
      ON I.FISH_TYPE = N.FISH_TYPE
    WHERE I.LENGTH = (
        SELECT MAX(I2.LENGTH)
        FROM FISH_INFO I2
        WHERE I.FISH_TYPE = I2.FISH_TYPE)
    ORDER BY 1

 

now me

On my github