How to use "Partition By" or "Max"?

select year, x,y
from (
      select year, x, y, max(year) over(partition by x) max_year
      from my data
      )
where  year = max_year

select * from (
  select year, x, y, row_number() over (partition by x order by year desc ) rn 
  from my_data
) where rn = 1

You could also be portable and use an OUTER JOIN :

select t1.year, t1.x, t1.y
  from my_data t1
  left join my_data t2
    on t2.x = t1.x
   and t2.year > t1.year
 where t2.x is null

Tags:

Sql

Oracle

Max

Related