Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    13

    Unanswered: Removing duplicate rows

    Hi All,

    Can anyone tell me how to remove all duplicate rows( Rows for which all column values are equal) from a table using SQL.

    Eg: Suppose table A has the following records.
    1 A 100
    1 A 100
    1 B 200
    1 C 300
    1 B 200

    The SQL query should return only first and 4th row here.

    Thanks,
    Sumesh

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can anyone tell me how to remove all duplicate rows
    This is a FAQ.
    Please SEARCH this forum or learn how to use GOOGLE.
    SQL SELECT returns a (result) set of rows which are of indeterminate order; therefore "1st" & "4th" do not really exist.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sumesh.abraham
    The SQL query should return only first and 4th row here.
    what happens to the 3rd and 5th rows?

    they are duplicated, they are different than the other rows, but both must be removed? why???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2006
    Posts
    13
    Sorry. The query should return 1st, 4th and 5th rows since others are duplicates.

  5. #5
    Join Date
    Nov 2006
    Posts
    13
    Hi,

    If the removal of duplicates is based on one field, say column1 and if there is a primary key column defined in the table,say col the query can be used as
    Code:
    delete from mytable where exists(select * from mytable as temptab 
    where temptab.column1=mytable.column1 and temptab.col > mytable.col)
    But what
    1) if there is no primary key defined and
    2) Value of all the columns of each row should be checked for duplicate

    I'd appreciate if anyone can pour in some thoughts.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What if all columns have to be checked? How do you mean, what if? Isn't it obvious - you'd have to check ALL columns. Hundreds of them? Sure! Hundreds of them, one by one!

  7. #7
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    http://download-east.oracle.com/docs...htm#sthref1981
    you can insert data into new table using this function.
    primary key is not necessary

    second option is insert into another table using distinct clause
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

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