Home » SQL & PL/SQL » SQL & PL/SQL » display of duplicate rows from same table
display of duplicate rows from same table [message #232783] Mon, 23 April 2007 04:50 Go to next message
arunsuresh100
Messages: 38
Registered: December 2006
Member
hi
how to get the duplicate rows from the same table?
please help me out in writing the query

Re: display of duplicate rows from same table [message #232784 is a reply to message #232783] Mon, 23 April 2007 04:54 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Search this forum and you may find --> THIS
Re: display of duplicate rows from same table [message #232785 is a reply to message #232783] Mon, 23 April 2007 04:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You can use GROUP BY ... HAVING COUNT(*) > 1:
SQL> WITH yourtable AS
  2   (
  3     SELECT 1 col_1, 'AB' col_2 FROM dual UNION ALL
  4     SELECT 2 col_1, 'CD' col_2 FROM dual UNION ALL
  5     SELECT 3 col_1, 'CD' col_2 FROM dual UNION ALL
  6     SELECT 1 col_1, 'CD' col_2 FROM dual UNION ALL
  7     SELECT 2 col_1, 'AB' col_2 FROM dual UNION ALL
  8     SELECT 3 col_1, 'CD' col_2 FROM dual UNION ALL  --> duplicate
  9     SELECT 1 col_1, 'AB' col_2 FROM dual            --> duplicate
 10   )
 11  SELECT col_1
 12       , col_2
 13  FROM yourtable
 14  GROUP BY col_1, col_2 HAVING COUNT(*) > 1
 15  /

     COL_1 COL_2
---------- ----------
         1 AB
         3 CD


MHE
Re: display of duplicate rows from same table [message #232789 is a reply to message #232785] Mon, 23 April 2007 05:05 Go to previous message
arunsuresh100
Messages: 38
Registered: December 2006
Member
hi friends
thanks for the answer
Previous Topic: Indexes - Disdavantages
Next Topic: wrong filesize with utl_file.fgetattr ?
Goto Forum:
  


Current Time: Fri Dec 09 23:13:07 CST 2016

Total time taken to generate the page: 0.23452 seconds