如何使用“分区依据"；或“最大"?

问题描述：

我有下表(my_data):

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

最好/最小的SQL语句是什么，以便仅检索与最高年份相关并按'X'分组的数据，像这样:

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.

答

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