[thelist] Duplicate Records in SQL

[Kayode yusuf]

Simple : try
delete from tblFOO a
where ID = ( Select MAX(ID)
             From   tblFOO b
            WHERE  b.Name = a.Name
            AND    b.Category = a.Category
            AND    b.LastDate = a.LastDate)


BOL

KOY

--- Jay Blanchard
<jay.blanchard at niicommunications.com> wrote:
> Howdy group!
>
> I have a table where I want to find duplicate
> records, and eliminate them
>
> tblFOO
> ID     Name     Category     LastDate
> 1      Tom      HR           2002-03-05
> 2      Dick     R&D          2002-03-05
> 3      Harry    Mgmt         2002-03-05
> 4      Tom      HR           2002-03-05
> 5      Moe      Mgmt         2002-03-06
> 6      Larry    BOD          2002-03-05
> 7      Curly    Support      2002-03-06
>
> SELECT Name, Category, Date
> FROM tblFOO
> WHERE Name = Name
> AND Category = Category
> AND LastDate = LastDate
>
> returns all records, because 'Name = Name' is true,
> as are the other items.
> In the table where I am doing this I would not know
> any of the values, so I
> cannot be more specific about criteria. The ID
> number for each record is
> unique. This is in MySQL, so there is no UNION
> available. Has anybody done
> this before?
>
> TIA!
>
> Jay
>
>
>
> --
> For unsubscribe and other options, including
> the Tip Harvester and archive of thelist go to:
> http://lists.evolt.org Workers of the Web, evolt !


=====


__________________________________________________
Do You Yahoo!?
Yahoo! Health - your guide to health and wellness
http://health.yahoo.com