a sql query from oracle

Question

I am using oracle 10g EE database.I have one table mytable and has two columns and data is as follows:

Note: I want to find out data based on same value in 2nd column only, it does not matter whether there exists same or different value in first column.

10 is repeated 3 times for A, B and C and these 3 are required output

similarly 20 is repeated 2 times for C and D and these are also required output

 column1 column2
-------------- ---------------
 A 10 //required
 A 10 //required 
 B 10 //required
 C 20//required
 D 20//required
 E 30--------not required as 30 is only here and not duplicated
 F 40--------not required as 40 is only here and not duplicated

following output is required i.e. same value in 2nd column having same or different values in 1st column

 column1 column2
-------------- ---------------
 A 10 
 A 10 
 B 10
 C 20
 D 20

• arnep: So you want rows where column2 has duplicate/triplicate/... values?
• saroj: @arnep exactly i want what you said, rows having column2 has duplicate/triplicate/... values

Answer 1

SELECT column1,
 column2
 FROM <table> t1
 WHERE column2 IN (SELECT column2
 FROM <table> t2
 GROUP BY column2
 HAVING count(*) > 1);

• saroj: ya i have run your query but still to get response from oracle, i have waited
• Ollie: @saroj, how large is your table? What columns are indexed etc?
• saroj: almost 2900 billion records are there, a huge database hence perhaps taking time
• Ollie: @saroj, unfortunately you'll always have a full table scan on your table as you are having to group the results and the optimiser cannot know if there are duplicate column2 values until it has scanned all the rows. If you could limit the records scanned in some other way (another, preferably indexed, column on the table perhaps) then the query would run faster but with what you have given us so far it's the best that can be offered.
• Ollie: @saroj, if you can then that would be preferable. If your table has 2900 billion records then is it partitioned? Do you need to scan the whole table when you could perhaps just scan the most relevent partition? A FTS on 2900 billion records will take some time no matter what server is running it. :-)

Answer 2

It sounds like you want

SELECT *
 FROM table_name t1
 WHERE column2 IN( SELECT column2
 FROM table_name t2
 GROUP BY column2
 HAVING COUNT(*) > 1 )

This appears to work with your sample data

SQL> with table_name as (
 2 select 'A' column1, 10 column2 from dual union all
 3 select 'A', 10 from dual union all
 4 select 'B', 10 from dual union all
 5 select 'C', 20 from dual union all
 6 select 'D', 30 from dual)
 7 SELECT *
 8 FROM table_name t1
 9 WHERE column2 IN( SELECT column2
 10 FROM table_name t2
 11 GROUP BY column2
 12 HAVING COUNT(*) > 1 );
C COLUMN2
- ----------
B 10
A 10
A 10

Answer 3

select * from table where column2 in ( select column2 from table group by coulmn2 having count(*)>1);

should work for you.

Thanks Abhi