CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Mauro Assis
Mauro Assis

Reputation: 435

Get just one row per ID from SQL query with specifc condition

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?

Upvotes: 2

Views: 3578

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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:

Upvotes: 1

Hogan
Hogan

Reputation: 70523

 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?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use distinct on if you want one row per id:

select distint on (id) t.*
from t
order by id, year desc;

Upvotes: 0

Related Questions