Home » SQL & PL/SQL » SQL & PL/SQL » Returning All Duplicate Rows
icon9.gif  Returning All Duplicate Rows [message #200895] Wed, 01 November 2006 16:05 Go to next message
BigDawg32
Messages: 1
Registered: November 2006
Junior Member
I have written the below code to return a certain subset of numbers:

SELECT column1, column2
FROM   table where column6 is null
GROUP BY column1, column2 HAVING COUNT(*) > 1 order by column1, column2;


So I get the following result:

Column1 Column2
1 2
5 3
2 7

These results are fine, they give me all of the duplicate records I need. I can then write
select * from table where column1='1' and column2 = '2'
What I am trying to do is return all nine columns of data that contain a 1 in column 1 and a 2 in column 2, a 5 in column1 and a 3 in column2, etc. There are 300 rows of data returned by my original query and I would like to see every row and record with the data returned from the above query. Here is what I tried, but it's not working (obviously).

select * from table 
where column1, column2 in 
(SELECT column1, column2
FROM   table where column6 is null
GROUP BY column1, column2 HAVING COUNT(*) > 1);
Re: Returning All Duplicate Rows [message #200913 is a reply to message #200895] Wed, 01 November 2006 20:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It would have helped if you posted the error message.

Since you think we enjoy a challenge, the correct syntax is below, but I'm not going to tell you what I changed. Wink

select * from table 
where column1, column2 in 
(SELECT (column1, column2)
FROM   table where column6 is null
GROUP BY column1, column2 HAVING COUNT(*) > 1);


Ross Leishman
Re: Returning All Duplicate Rows [message #200991 is a reply to message #200895] Thu, 02 November 2006 02:54 Go to previous message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Same as rleishman told

select * from table
where (column1, column2) in
(SELECT column1, column2
FROM table where column6 is null
GROUP BY column1, column2 HAVING COUNT(*) > 1);
Previous Topic: How to update a table with subquery returning more than one row
Next Topic: Renaming Tablespace name
Goto Forum:
  


Current Time: Mon Dec 05 07:04:30 CST 2016

Total time taken to generate the page: 0.11470 seconds