DDL-table name(200,000 data) sheet2; Inv_num number(10), Price varchar2(20)
Inv_num price
9606 X
9613 X
9620 X
9644 X
9668 $34.56
6401 X
6401 $ 52.32
4720 X
4720 $ 539.03
4737 $ 924.05
4737 X
4782 X
4782 $ 89.40
I want to have the following result(not duplicated record + remove one of duplicated record with price 'x');
Inv_num price
9606 X
9613 X
9620 X
9644 X
9668 $34.56
6401 $ 52.32
4720 $ 539.03
4737 $ 924.05
4782 $ 89.40
I did it but it is too slow, would you revise my query thanks
SELECT Inv_num,price
FROM Sheet2
where Sheet2.Inv_num in(
SELECT Sheet2.Inv_num FROM Sheet2 where Sheet2.Inv_num in(
SELECT Sheet2.Inv_numFROM Sheet2
GROUP BY Sheet2.Inv_num HAVING (((Count(Sheet2.Inv_num))>1)))) and Sheet2.price <>'x'
union
SELECT Inv_num,price
FROM Sheet2
where Sheet2.Inv_num not in(
SELECT Sheet2.Inv_num FROM Sheet2 where Sheet2.Inv_num in(
SELECT Sheet2.Inv_num FROM Sheet2
GROUP BY Sheet2.Inv_num
HAVING (((Count(Sheet2.Inv_num))>1))))
thanks in advance