OraFAQ Forum: SQL & PL/SQL » How can I get the distinct rows?

Home » SQL & PL/SQL » SQL & PL/SQL » How can I get the distinct rows? (oracle)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can I get the distinct rows? [message #621161]

Sun, 10 August 2014 21:36

zhuyeq
Messages: 3
Registered: August 2014

Junior Member

I'm not professional Embarassed

I want to get the "right result",(see picture)
but when select from large data ,
its takes a long time using "group by" or "distinct",
have any other "professional" sentence ?
Thanks!

select
test1.id1,
test1.id2,
test1.doctor,
test1.ordering_d,
test2.ITEM_NO,
test2.item_name,
test2.amount,
test2.COSTS
FROM test1 ,test2
WHERE test1.id1=test2.id1
AND test1.id2=test2.id2
AND test1.order_code = test2.item_code
AND test2.item_name like 'test drug 1'
GROUP BY test1.id1,tet1.id2,doctor,ordring_d,item_no,item_name,amount,costs

/forum/fa/12095/0/

Attachment: question.JPG
(Size: 182.32KB, Downloaded 1076 times)

[Updated on: Sun, 10 August 2014 21:47]

Report message to a moderator

Re: How can I get the distinct rows? [message #621162 is a reply to message #621161]

Sun, 10 August 2014 21:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

WHERE test1.id1=test2.id1
AND test1.id2=test2.id2
AND test1.order_code = test2.item_code
AND test2.item_name like 'test drug 1'

are all columns in the WHERE clause (above)indexed?

>AND test2.item_name like 'test drug 1'
above makes little sense.
LIKE usually involves wild card characters like below

AND test2.item_name like '%test drug 1%'

otherwise it could be replaced by below

AND test2.item_name = 'test drug 1'

Report message to a moderator

Re: How can I get the distinct rows? [message #621163 is a reply to message #621162]

Sun, 10 August 2014 22:06

zhuyeq
Messages: 3
Registered: August 2014

Junior Member

"test1.id1" , "test2.id1" indexed

Report message to a moderator

Re: How can I get the distinct rows? [message #621164 is a reply to message #621163]

Sun, 10 August 2014 22:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Might get faster results if all the below are indexed

AND test1.id2=test2.id2
AND test1.order_code = test2.item_code
AND test2.item_name like 'test drug 1'

Report message to a moderator

Re: How can I get the distinct rows? [message #621165 is a reply to message #621164]

Sun, 10 August 2014 22:14

zhuyeq
Messages: 3
Registered: August 2014

Junior Member

Thankyou BlackSwan !
I'll have a try

Report message to a moderator

Previous Topic:	list of invalid objects
Next Topic:	procedure with loop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:54:53 CDT 2024