www.webdeveloper.com
Results 1 to 2 of 2

Thread: Remove Duplicates

  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Remove Duplicates

    Hi all,

    I have the following data selected on order_id:



    These are order lines for an order, and as you can see they have been duplicated 3 times.

    The first 2 records are correct, the following 4 records (2 sets of 2) are duplicates.

    Some times the order lines have only 1 set of duplicates, sometimes they have 2 sets and so on.

    For each order_id I want to be able to remove the duplicates. Does anyone have any feedback as to how to go about doing this?

    Thanks!

    dai.hop

  2. #2
    Join Date
    Jul 2009
    Location
    Atlanta, GA
    Posts
    11
    I don't know the syntax for MySQL but its similar and the concept is the same:
    Code:
    IF OBJECT_ID('Test123', 'U') IS NOT NULL DROP TABLE Test123
    Create Table Test123
    (
      PK int identity(1000, 1) PRIMARY KEY,
      OrderId int,
      Field1 varchar(10),
      Field2 varchar(10),
      Field3 varchar(10),
    )
    GO
    Insert Into Test123 (OrderId, Field1, Field2, Field3) Values (50, 'Scott', 'Hello', 'World')
    Insert Into Test123 (OrderId, Field1, Field2, Field3) Values (50, 'Scott', 'Hello', 'World')
    Insert Into Test123 (OrderId, Field1, Field2, Field3) Values (50, 'Scott', 'Hello', 'World')
    Insert Into Test123 (OrderId, Field1, Field2, Field3) Values (50, 'Scott', 'Hello', 'World')
    Insert Into Test123 (OrderId, Field1, Field2, Field3) Values (51, 'Scott', 'Hello', 'World2')
    Insert Into Test123 (OrderId, Field1, Field2, Field3) Values (52, 'Scott', 'Hello', 'World3')
    Insert Into Test123 (OrderId, Field1, Field2, Field3) Values (52, 'Scott', 'Hello', 'World3')
    GO
    Delete From Test123 Where PK In
    (
      Select PK
      From Test123
      Where PK <>
      (
        Select Min(x.PK) --Keep the first record
        From Test123 x
        Where x.OrderId = Test123.OrderId
      )
    )
    Scott Knake
    Custom Software Development
    Apex Software, Inc.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles