Home » SQL & PL/SQL » SQL & PL/SQL » query a table
query a table [message #191225] Tue, 05 September 2006 04:50 Go to next message
Raoul
Messages: 20
Registered: April 2005
Junior Member
Hi,

Could someone please help me with the following:
I have a table TAB which has 2 fields FIELD1(key) and FIELD2. How do I ONLY display the records where FIELD2 value is the same in more than one record. Example

Field1 Field2
A x
B y
C z
D x
E x

Here, I only want to display record 1,4 and 5 since FIELD2 value is the same and there is more than 1 record where FIELD2 value is the same.

Many thanks
icon2.gif  Re: query a table [message #191228 is a reply to message #191225] Tue, 05 September 2006 05:03 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
try using ROWNUM,but i am not sure whether that would help fieldwise..
Re: query a table [message #191230 is a reply to message #191225] Tue, 05 September 2006 05:05 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


 try this.

SELECT * FROM TESTING T1 WHERE EXISTS(
SELECT COL2 FROM TESTING T2 WHERE T1.COL2=T2.COL2 GROUP BY COL2 
HAVING COUNT(*)>1)ORDER BY COL2




regards,
Re: query a table [message #192421 is a reply to message #191225] Tue, 12 September 2006 05:05 Go to previous message
Raoul
Messages: 20
Registered: April 2005
Junior Member
Many thanks hananjay, this worked like a charm!
Previous Topic: ALTER TABLE stmt for modified CHECK constraint for Oralce 9.x
Next Topic: Export to CSV issue
Goto Forum:
  


Current Time: Thu Dec 08 00:16:50 CST 2016

Total time taken to generate the page: 0.09352 seconds