1

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.

lemon
  • 14,875
  • 6
  • 18
  • 38
Bruno Mello
  • 4,448
  • 1
  • 9
  • 39
  • 1
    Can you share your best coding attempt at this problem? – lemon Sep 19 '22 at 12:21
  • I think you'll find the answer here: https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group – johey Sep 19 '22 at 12:35

3 Answers3

3

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.

lemon
  • 14,875
  • 6
  • 18
  • 38
2

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

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
2

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;
trincot
  • 317,000
  • 35
  • 244
  • 286