Question 114568 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search