Re: Query to select only the duplicate records from a table?

From: bunyamin karadeniz <bunyamink_at_havelsan.com.tr>
Date: Sun, 8 Oct 2000 09:45:18 +0300
Message-Id: <10672.121277@fatcity.com>

SELECT interested_columns
FROM table_name a
WHERE rowid >(SELECT min(rowid)
FROM table_name b
WHERE b.pk_column = a.pk_column ) ;

*pk_coloumn is the primary key column the table. if you want to delete the duplicate rows, just change the select to DELETE

• if you want to leave only one row of the duplicated rows,For ex: you have 3 duplicated rows and want to delete 2 of them and leave only one, The only possibility as I understand is first selecting the duplicate rows with DISTINCT and then insert into another table and then delete duplicated rows from the base table and select from second table and insert into base table.******

  ---

Bunyamin K. Karadeniz
Enformasyon Teknolojileri Dir.
Veri Tabani Grubu tel : 2873565/1681
HAVELSAN

---

> -----Original Message-----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Monday, November 06, 2000 2:38 PM
>
>
> >Hello Lists,
> >
> >Can any body help me in writing a query which will
> >list only the duplicate records from a table?
> >
> >Any help would be highly appreciated.
> >
> >TIA,
> >Muru
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Thousands of Stores. Millions of Products. All in one Place.
> >http://shopping.yahoo.com/
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Murugesan Balakrishnan
> > INET: mail_oracle_at_yahoo.com
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jim Walski
> INET: jwalski3_at_classicplan.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Sun Oct 08 2000 - 01:45:18 CDT