sqloracle-databasemax

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

I've the following table (my_data):

year |  X  |  Y
-----+-----+-----
2010 |  A  |  10
2011 |  A  |  20
2011 |  B  |  99
2009 |  C  |  30
2010 |  C  |  40


what is the best / smallest SQL statement to retrieve only the data related to the highest year and grouped by 'X' , like this:

year |  X  |  Y
-----+-----+-----
2011 |  A  |  20
2011 |  B  |  99
2010 |  C  |  40


Note that this result table will be used in a join.

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