Skip to Main Content

my query is too slow

Jimmie_MMay 28 2009 — edited May 29 2009
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
This post has been answered by Boneist on May 28 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Jun 26 2009
Added on May 28 2009
10 comments
2,601 views