PostgreSQL partition by and select first row with specific column value

Question

I have data in the following format:

ID      DATE       METRIC
1      1/1/19        1
1      1/3/19        1
1      1/5/19        0
2      1/2/19        0
2      1/9/19        0
2      1/11/19       0
3      1/1/19        0
3      1/2/19        0
3      1/3/19        1

What I am trying to accomplish is only take one row per ID and if there is a 1 metric take the first date with a 1. If there is not a 1 metric take the row and NULL the date. My desired output would look like this:

ID      DATE       METRIC
1      1/1/19        1
2       NULL         0
3      1/3/19        1

The closest I have come is doing a row_number() OVER (PARTITION BY ID order by DATE) as RN however that just leaves me with numbered rows per ID. Is it possible to do a case when within a partition by?

Answer 1

You can use DISTINCT ON and conditional logic:

select distinct on(t.id)
    t.id,
    case when t.metric = 1 then t.date end date,
    metric
from mytable t
order by t.id, t.metric desc, t.date

Demo on DB Fiddle:

id | date       | metric
-: | :--------- | -----:
 1 | 2019-01-01 |      1
 2 | null       |      0
 3 | 2019-03-01 |      1