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

So you want rows where column2 has duplicate/triplicate/... values? — arnep
– arnep, Commented Apr 18, 2012 at 9:55
@arnep exactly i want what you said, rows having column2 has duplicate/triplicate/... values — saroj
– saroj, Commented Apr 18, 2012 at 9:57

Ollie · Accepted Answer · 2012-04-18 09:55:59Z

3

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

answered Apr 18, 2012 at 9:55

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

saroj Over a year ago

ya i have run your query but still to get response from oracle, i have waited

Ollie Over a year ago

@saroj, how large is your table? What columns are indexed etc?

saroj Over a year ago

almost 2900 billion records are there, a huge database hence perhaps taking time

Ollie Over a year ago

@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 Over a year ago

@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. :-)

|

Justin Cave · Accepted Answer · 2012-04-18 09:55:14Z

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

abhi · Accepted Answer · 2012-04-18 10:08:48Z

0

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

should work for you.

Thanks Abhi

answered Apr 18, 2012 at 10:08

abhi

6211 gold badge7 silver badges17 bronze badges

Collectives™ on Stack Overflow

a sql query from oracle

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related