Query to delete rows by comparing two columns for being distinct?

[Phire]

I need to delete rows where two specific columns may have duplicate values between other rows, but I want to keep at least one, so I have at least 1 unique record. So for example:

(Name) (Value 1) (Value 2) (State) (Date)

John Dough 12345 10141 Nebraska 4/04/09
John Dough 54321 10284 Nebraska 4/05/09
John Dough 12345 10141 Nebraska 4/06/09

I would want the 3rd row deleted because its Value 1 and Value 2 are the same as row one's. Anyone have any ideas for a query?

[tomhath]

The easy way is to use an "exists" subselect. Do you have an id column? Or can you be sure there will only be one for each date?

delete from The_Table t1 
where exists (
        select 1 from The_Table t2
        where t2.Value1 = t1.Value1
            and t2.Value2 = t1.value2 
            and t2.date < t1.date
        )

[bradgrafelman]

And is there any reason you'd ever want the data to get into your DB in the first place? If not, perhaps there should be a UNIQUE index on those two columns?