How can I drop a row if repeated by category?-CodePudding

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?

CodePudding user response：

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.)

CodePudding user response：

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'
)