Delete all records with most recent date

[NZArchie]

Has anyone got TOP syntax working in a DELETE query? I am trying to delete all records with the most recent date. The syntax works with SELECT eg

SELECT TOP 1 Payments.DateOfPayment, Payments.*
FROM Payments
ORDER BY Payments.DateOfPayment;

But doesn't port to DELETE. Clues?

 

[John Big Booty]

What you need to do is use your Select Top query as the basis for your Delete query.

 

[MarkK]

I would use a subquery like ...

DELETE FROM Payments 
WHERE DateOfPayment = (
  SELECT MAX(DateOfPayment) FROM Payments 
  )