SQL 2000 Delete Duplicates

[Mike Kopa]

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

[gopi]

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
>

[R Lainio]

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
>

[R Lainio]

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