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
  • 605,456
  • 145
  • 1,078
  • 1,228
Mauro Assis
  • 375
  • 1
  • 5
  • 22

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
  • 605,456
  • 145
  • 1,078
  • 1,228
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,242,037
  • 58
  • 646
  • 786
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
  • 69,564
  • 10
  • 76
  • 117