Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    22

    Unanswered: Delete duplicates........How?

    How do i delete duplicate records but keep the record with the lowest value?

    EG:
    From the table below i only want to keep the records highlighted in red.

    ProdCode - Price
    -----------------------
    22 - 1.05
    22 - 1.00
    22 - 1.10
    33 - 3.31
    33 - 3.33
    44 - 4.40
    44 - 4.42
    44 - 4.41
    44 - 3.99

    How would i go about that in SQL 2005?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This are not true duplicate records as they differ in the value of at least one column.

    Try
    Code:
    DELETE 
    FROM DaTable
    Where EXISTS (SELECT 1 
    		FROM DaTable as T2
    		WHERE DaTable.ProdCode = T2.ProdCode AND
    			DaTable.Price > T2.Price)
    This solution will keep multiple records with the same price.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2005
    Posts
    22
    Thanks I'll give it a try.

    If it helps each record has a unique record id.

Posting Permissions

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