SQL group by get value on one column based on order of another column

Question

Suppose I have the following table in SQL:

id	year	value
1	2022	10
1	2020	5
2	2019	10
2	2021	4
3	2018	2
3	2017	10

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

id	year	value
1	2022	10
2	2021	4
3	2018	2

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.

Answer 1

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.