select x where latest y of x is z

1_schema.sql

create table tasks (
  id serial primary key,
  name text not null
);

create table results (
  id serial primary key,
  created_at timestamptz default now() not null,
  state text default null,
  task_id int references tasks(id)
);

insert into tasks (name) values ('task_1');
insert into tasks (name) values ('task_2');
insert into tasks (name) values ('task_3');
insert into tasks (name) values ('task_4');
insert into tasks (name) values ('task_5');

insert into results (state, task_id) values ('completed', 1);
insert into results (state, task_id) values ('failed', 2);

2_query.sql

begin;

select            task.*
from              tasks task
left join lateral (
                    select   _r.state
                    from     task_results _r
                    where    _r.task_id = task.id
                    order by _r.created_at desc
                    limit    1
                  ) result on true
where             task.name = $1::text
and               (result is null or result.state not in ('completed', 'failed'))
limit             1
for update of     task
skip locked
;

commit;