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
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
Last Comment
slightwv (䄆 Netminder)
SOLUTION
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.
SOLUTION
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.
SOLUTION
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.
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 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"
minus
select id, "damaged" from table where status = 'Repaired"
SOLUTION
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.
ASKER CERTIFIED SOLUTION
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.
select * from Products where prod_id not in (select prod_id from products where status='Repaired');