How can I drop a row if repeated by category?

Question

how would I go about dropping rows which have a duplicate and keeping another row by its category.

For example, let's consider a sample table

Item | location | Status
------------------------
123  |   A       |  done
123  |   A       |  not_done
123  |   B       |  Other
435  |   D       |  Other

So essentially what I want to get to would be this table

Item | location | Status
------------------------
123  |   A       |  done
435  |   D       |  Other

I am not interested in the other status or location IF the status is done. If it is not "done" then I would show the following one.

Any clues if it is possible to create something like this in an SQL query?

Answer 1

Identify rows with done if any and prioritize them.

select * except rn
from (
  select item, location, status
       , row_number() over (
           partition by item
           order by case status when 'done' then 0 else 1 end
         ) as rn
  from t
)
where rn = 1

(I didn't try it, excuse syntax errors please.)

Answer 2

Yes you can do it via exists condition like below after enabling standard SQL first.

select * from
yourtable A 
where not exists
(
select 1 from yourtable B 
where A.id=B.id and A.location=B.location
and A.status<>B.status 
AND B.status <> 'done'
)