OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL query (Oracle11G)

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

SQL query [message #599098]

Tue, 22 October 2013 04:10

poojamunj
Messages: 64
Registered: May 2011
Location: MUMBAI

Member

Hi,

I want to develop a query through which I should be able to fetch records which is common to different records of the same table. For eg: Table: ISID, Columns :Id_cntxt_typ, instr_id.
Instr_id Id_cntxt_typ
1 A
1 B
2 A
3 B
3 A
4 A
5 A
5 B

Expected output:
Instr_id
1
3
5

Please help me in forming the query for getting the said output.

Thanks,
Pooja

Report message to a moderator

Re: SQL query [message #599101 is a reply to message #599098]

Tue, 22 October 2013 04:26

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Have a look at Analytic count or the HAVING clause depending on the complexity of the "real" data.

Report message to a moderator

Re: SQL query [message #599102 is a reply to message #599098]

Tue, 22 October 2013 04:27

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

Group by Instr_id and choose those HAVING COUNT(*) >= 2

[Edit:] Didn't see Roachcoach had already posted. Smile

[Updated on: Tue, 22 October 2013 04:32]

Report message to a moderator

Re: SQL query [message #599109 is a reply to message #599102]

Tue, 22 October 2013 04:50

poojamunj
Messages: 64
Registered: May 2011
Location: MUMBAI

Member

Thanks all, I used intersect command, Even by that m getting the desired output.
My 2nd question is, I want to copy the entire record having id_cntxt_typ as 'B' to the record having id_cntxt_typ as 'A' which are having same instr_id (1,3,5).
Please help me in forming this query.

Report message to a moderator

Re: SQL query [message #599112 is a reply to message #599109]

Tue, 22 October 2013 04:58

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

That doesnt make sense to me, there are too many ways it can be interpreted.

Please post a test case showing desired outputs before and after.

Report message to a moderator

Previous Topic:	ORA-06550 PLS-00201: identifier must be declared Issue.
Next Topic:	With Clause Not working

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 20:35:42 CDT 2024