SQL 2000 Delete Duplicates


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: SQL 2000 Delete Duplicates

  1. #1
    Mike Kopa Guest

    SQL 2000 Delete Duplicates


    I have a Products Table (ProdID, Prodname, Category, DateEntered), many of
    the items in the Prodname field are duplicates and I need to keep only the
    last 3 entered for a particular Prodname. The DateEntered will determine
    which ones will be kept.

    EX: prodname Category DateEntered
    Apples red 12/01/2000
    Apples green 11/28/2000
    Apples yellow 11/25/2000
    Apples macintosh 10/22/2000
    Apples granny 10/11/2000

    In this case I would only want to keep the top three records. Any ideas
    as to how I might approach such a delete of duplicates?
    Your help is greatly appreciated! This is driving me up the wall!
    Thanks
    Mike


  2. #2
    gopi Guest

    Re: SQL 2000 Delete Duplicates


    before execute your query, you execute this statement then
    try.It will be ok for you.

    SET ROWCOUNT 3

    If you need more information let me know.



    "Mike Kopa" <mpkopa@socantel.net> wrote:
    >
    >I have a Products Table (ProdID, Prodname, Category, DateEntered), many

    of
    >the items in the Prodname field are duplicates and I need to keep only the
    >last 3 entered for a particular Prodname. The DateEntered will determine
    >which ones will be kept.
    >
    >EX: prodname Category DateEntered
    > Apples red 12/01/2000
    > Apples green 11/28/2000
    > Apples yellow 11/25/2000
    > Apples macintosh 10/22/2000
    > Apples granny 10/11/2000
    >
    >In this case I would only want to keep the top three records. Any ideas
    >as to how I might approach such a delete of duplicates?
    >Your help is greatly appreciated! This is driving me up the wall!
    >Thanks
    >Mike
    >



  3. #3
    R Lainio Guest

    Re: SQL 2000 Delete Duplicates


    Hi

    Test this

    delete from products where id not in
    (select top 3 id from products order by dateentered desc)
    and prodname = 'apples'

    Regards,
    Rainer Lainio


    "Mike Kopa" <mpkopa@socantel.net> wrote:
    >
    >I have a Products Table (ProdID, Prodname, Category, DateEntered), many

    of
    >the items in the Prodname field are duplicates and I need to keep only the
    >last 3 entered for a particular Prodname. The DateEntered will determine
    >which ones will be kept.
    >
    >EX: prodname Category DateEntered
    > Apples red 12/01/2000
    > Apples green 11/28/2000
    > Apples yellow 11/25/2000
    > Apples macintosh 10/22/2000
    > Apples granny 10/11/2000
    >
    >In this case I would only want to keep the top three records. Any ideas
    >as to how I might approach such a delete of duplicates?
    >Your help is greatly appreciated! This is driving me up the wall!
    >Thanks
    >Mike
    >



  4. #4
    R Lainio Guest

    Re: SQL 2000 Delete Duplicates


    Hi agin

    Sorry for my last answer. I send you the wrong sql query.

    Here is the right query to test.

    delete from products where prodid not in
    (select top 3 prodid from products where prodname = 'apples' order by dateentered
    desc)

    Regards,
    Rainer Lainio

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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center