Link to home
Create AccountLog in
Avatar of Shaunsmith
ShaunsmithFlag for United States of America

asked on

Get Unique Row satisfying two conditions

I have a Products Table which has a Status column . The Status defines whether the Item is damaged ,repaired,inMaintainance etc

eg:

Prd Id      Status      

1      damaged      
1      Repaired
2      damaged
3      inMaintainance

I was trying to build a query that would give me all the Parts that are damaged but 'not repaired'



select * from products where Status='damaged' And Status not in 'Repaired'


This would give me

Prd Id      Status
1      damaged
2      damaged

But I should not get Product Id =1 as Technically the product has been sent to Repair. The results should be


 Prd Id      Status
2      damaged
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Off the top of my head:

select * from Products where prod_id not in (select prod_id from products where status='Repaired');
SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Small correction to the one above:

select prod_id, status from (
      select prod_id, status, row_number() over( partition by prod_id order by case status when 'Repaired' then 0 when 'damaged' then 1 else 2 end) myrownum
      from tab1
)
where status='damaged' and myrownum=1;
select id, status from table where status = 'damaged"
minus
select id, "damaged" from table where status = 'Repaired"
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account