remove duplicate record
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: remove duplicate record

  1. #1
    Join Date
    Aug 2006
    Posts
    1

    remove duplicate record

    i have table


    roll no name serial

    1 name1 123
    1 name2 121
    1 name3 120
    1 name4 124
    2 name 125



    this table have duplicate recorcd in rollno field but my doubt how to be remove the duplicate record except first row please tell me sql command?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2001
    Location
    UK
    Posts
    45

    removing dup recs

    delete from table_name where rowid not in(
    select min(rowid) from table_name group by dup_col_name)

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    The trouble with Googling in this case is that it tends to return pre-8i solutions using self-joins, and not the potentially more efficient ones using analytic functions. For example, why not something like:

    Code:
    DELETE mytable
    WHERE  rowid IN
           ( SELECT LEAD(rowid) OVER (PARTITION BY roll_no ORDER BY name, serial)
             FROM   mytable );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width