5 Replies Latest reply: Sep 20, 2013 7:01 AM by n2813 RSS

    removing duplicates except one

    salute-Salem

      db and dev 10g rel2 ,

      hi all

      , suppose that i have a table with a lot of duplicate rows ,

      what i need is to delete the duplicates and retain one row of these duplicates .

       

      like

      column -- with those values .........

      hi

      hi

      hi

       

      how to delete two (hi's) and retain the third , ?

      it is all applied to all the duplicate values in the column

       

      thanks in advance

        • 1. Re: removing duplicates except one
          Frank Kulash

          Hi,

           

          Here's one way:

           

          DELETE  table_x

          WHERE   ROWID  NOT IN (

                                    SELECT    MIN (ROWID)

                                    FROM      table_x

                                    GROUP BY  col_1

                               )

          ;

          When this finishes, exactly 1 row with each value of col_1 will remian.

           

          I hope this answers your question.
          If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
          If you're asking about a DML statement, such as DELETE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
          Explain, using specific examples, how you get those results from that data.
          See the forum FAQ: https://forums.oracle.com/message/9362002

          • 2. Re: removing duplicates except one
            salute-Salem

            i think you mean ,

            {code}

            DELETE from table_x   -- you forgot the (from) keyword .

            WHERE   ROWID  NOT IN (

                                      SELECT    MIN (ROWID)

                                      FROM      table_x

                                      GROUP BY  col_1

            {code}

            i think if i use (max) function , it is right too ? ?

            • 3. Re: removing duplicates except one
              SomeoneElse

              > you forgot the (from) keyword .

               

              FROM is not required in a delete statement.

               

              SQL> delete emp;

               

              14 rows deleted.

              • 4. Re: removing duplicates except one
                Frank Kulash

                Hi,

                 

                newbi_egy wrote:

                 

                i think you mean ,

                {code}

                DELETE from table_x   -- you forgot the (from) keyword .

                WHERE   ROWID  NOT IN (

                                          SELECT    MIN (ROWID)

                                          FROM      table_x

                                          GROUP BY  col_1

                {code}

                i think if i use (max) function , it is right too ? ?

                The FROM keyword is optional; you don't have to include it, but you can if you want to.  It won't change the results or performance.

                 

                Since we don't care which of the rows with a given value of col_1 we keep (as long as we keep exactly 1 of them), it doesn't matter if we use MIN or MAX.

                • 5. Re: removing duplicates except one
                  n2813

                  delete from table where rowid not in (select min(rowid) from table)