[ORACLE](Leet_Code) 1070. Product Sales Analysis III(ㅇ)

https://leetcode.com/problems/product-sales-analysis-iii/submissions/?envType=study-plan-v2&envId=top-sql-50

문제 해설

• 각 제품이 팔린 첫번째 년도를 구해라.

힌트

• partition by 사용.
• 서브쿼리 사용

시도1 : 실패

/* Write your PL/SQL query statement below */

select product_id, first_year, quantity, price 
from (
      select a.product_id, a.year as first_year , a.quantity, a.price,
            row_number() over (partition by a.product_id order by a.year) as rn
      from Sales a
)
where rn = 1

왜일까.. output확인해보니 중복값이 있었다 . 그러므로 row_number를 쓰면 안됨.

정답

/* Write your PL/SQL query statement below */
select product_id, first_year, quantity, price 
from (
      select a.product_id, a.year as first_year , a.quantity, a.price,
            rank() over (partition by a.product_id order by a.year) as rn
      from Sales a
)
where rn = 1