2

I'm using Postgres v>9.

I'd like to get values of a table like this:

id  year   value
1   2015   0.1
2   2015   0.2
6   2030   0.3
6   2015   0.4
6   2017   0.3

The idea is to get lines where years is < 2019 or year = 2030. If id is repeated, I´d like to get only 2030 line, not 2015 ones, that is, the result I´m looking for is:

id  year   value
1   2015   0.1
2   2015   0.2
6   2030   0.3

How can I do that?

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
Mauro Assis
  • 303
  • 2
  • 17

3 Answers3

1

This only considers the year 2030 or any year < 2019. At least that's what the question says. (I suspect there's something fuzzy there.)

It picks one row per id, with the latest year first.

SELECT DISTINCT ON (id) *
FROM   tbl
ORDER  BY id, year DESC
WHERE (year = 2030 OR year < 2019);

If there can be multiple rows with the same (id, year), you need a tiebreaker.
About this and more details for DISTINCT ON:

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
0

Use distinct on if you want one row per id:

select distint on (id) t.*
from t
order by id, year desc;
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
0
 SELECT ID,
        FIRST_VALUE(YEAR) OVER (PARTITION BY ID ORDER BY YEAR DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS year,
        FIRST_VALUE(Value) OVER (PARTITION BY ID ORDER BY YEAR DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS value  
 FROM t 
 WHERE YEAR = 2030 OR YEAR < 2019

I think this is the standard for first_value -- postgre might require a seperate clause?

Hogan
  • 65,989
  • 10
  • 76
  • 113