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.
Advertisement
Answer
select year, x,y from ( select year, x, y, max(year) over(partition by x) max_year from my data ) where year = max_year