Home » SQL & PL/SQL » SQL & PL/SQL » Filtering Data
Filtering Data [message #197428] Wed, 11 October 2006 04:26 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi All,

I have records like foloowing in TABLEA

ID Action
== =====
1 P1
1 P3
1 P4
2 P1
2 P2
2 P3
3 P1
3 P2
4 P3
4 P4

Suppose I want to list all the ID's which has the all the actions (P1, P2), how can write a SQL. In this case it should return ID - 2,3. because both P1 and P2 exists in ID 2&3.

Regards,
Re: Filtering Data [message #197437 is a reply to message #197428] Wed, 11 October 2006 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
One way is:
SELECT id FROM tablea WHERE action = 'P1'
INTERSECT
SELECT id FROM tablea WHERE action = 'P2'


I'm sure there'll be other ways along shortly.
Re: Filtering Data [message #197440 is a reply to message #197428] Wed, 11 October 2006 04:57 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Thank you very much.

Brayan
Re: Filtering Data [message #197499 is a reply to message #197428] Wed, 11 October 2006 08:32 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Without any insight into what, if any, indexes may be available, the following would prevent FTS'ing twice:

select id from (
	select id, row_number() over (partition by id order by action) rn
	from tablea
	where action in ('P1','P2')))
where rn = 2;
Previous Topic: Is commit & rollback is DCL command
Next Topic: What is Difference between Truncate and delete
Goto Forum:
  


Current Time: Fri Dec 09 23:14:24 CST 2016

Total time taken to generate the page: 0.09239 seconds