Suppose I have the following table in SQL:

And for each id, I want the last value based on the year. The final table would be:

I know I have to use some sort of group by in id than order by year and get the first value, but I don't know what aggregate function that would be.

My attempt was to group by id while ordering by year and then getting the first value:

SELECT id, MAX(year), FIRST(value)
FROM t
GROUP BY id
ORDER BY year desc

But this doesn't work.

CodePudding user response：

Yet another option is using the FETCH FIRST n ROWS WITH TIES clause, which allows to get the first rows with respect an ordering. Applying the ordering using the ROW_NUMBER window function, will make you extract all rows with ranking = 1, tied.

SELECT  * 
FROM tab 
ORDER BY ROW_NUMBER() OVER(PARTITION BY id_ ORDER BY year_ DESC)
FETCH FIRST 1 ROWS WITH TIES;

Check the demo here.

CodePudding user response：

You can use a window function:

 (partition by id order by year desc)

The first answer already gives the structure of the SQL making use of row_number() to filter the result. Here is an alternative:

select distinct id, 
       first_value(year)  over w as year,
       first_value(value) over w as value
from   t
window w as (partition by id order by year desc)
order by id;

CodePudding user response：

This is simple task for window functions:

with row_numbers as (
  select 
      *,
      row_number() over (partition by value order by year desc) rn
  from t
) select id, year, value from row_numbers where rn = 1;

online sql editor