How to delete duplicate values for a field in a table with a single query?

I want to delete from a table the entries where it has multiple values for Date field.

So say I have Employee table - Id,Name,Date,Points

I want to delete the entries with same Date field which should be unique...just to cleanup I need to just keep a single entry for date and delete the rest开发者_开发问答...maybe keep the recent one if possible....

Can anyone please suggest a update query to do this?

---

Use:

DELETE FROM EMPLOYEE
 WHERE id NOT IN (SELECT MAX(e.id)
                    FROM EMPLOYEE e
                   WHERE e.date = date
                GROUP BY e.date)

The GROUP BY isn't likely to be necessary due to the WHERE clause & only returning one column with an aggregate function on it, but it's included to be safe. This is assuming that when the OP says date field, being SQL Server 2008 that means the DATE data type, not DATETIME.

---

this query looks at records with same Id,Name and Points and deletes all but the latest

with cte as(
    select id,Name, Date, Points,row_number() over(
       partition by id,name,points order by date desc) as ind
    from emp)

    delete from cte where ind>1

---

If your table has primary key, you can join the table to itself by the dup condition and filter out greater PKs, something like the following:

delete e2
from Employee e
join Employee e2 on e.Date=e2.Date
where e.ID < e2.ID

---

you can use

DELETE
From Employee 
WHERE ID not in (select max(ID) from Employee group by Name)

if the last entry is the recent date, Or you can use this code

DELETE
From Employee 
WHERE ID not in 
(select max(ID) from Employee e1 
where Date=(select max(Date) From Employee where Name=e1.Name) 
group by Name)