CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10g
saroj
saroj

Reputation: 653

a sql query from oracle

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

Upvotes: 0

Views: 139

Answers (3)

abhi
abhi

Reputation: 621

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

should work for you.

Thanks Abhi

Upvotes: 0

Ollie
Ollie

Reputation: 17538

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

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231651

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

Upvotes: 3

Related Questions