Avatar of Shaunsmith
Shaunsmith
Flag 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
Oracle DatabaseSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
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
johnsone

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Neil Russell

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
slightwv (䄆 Netminder)

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;
awking00

select id, status from table where status = 'damaged"
minus
select id, "damaged" from table where status = 'Repaired"
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
awking00

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.