Deleting Maximum Values

[Ed]

Trying to structure a query that deletes the maximum value from a table or
other query. For example, in a field with dates, would delete the date that
is the furthest out:

Dates
31/01/07
28/02/07
31/03/07
30/04/07

ie. April 30, 2007 would be deleted leaving Jan, Feb and March.

Thanks in advance for any and all suggestions!

 

[Wolfgang Kais]

Hello Ed.

Trying to structure a query that deletes the maximum value from a
table or other query. For example, in a field with dates, would
delete the date that is the furthest out:

Dates
31/01/07
28/02/07
31/03/07
30/04/07

ie. April 30, 2007 would be deleted leaving Jan, Feb and March.

Thanks in advance for any and all suggestions!

How about this:
Delete from [YourTableName] Where [date] =
(Select max([date]) from [YourTableName])

You should backup the table before testing.

 

[John Nurick]

Hi Ed,

Something like this should do the trick:

DELETE FROM My Table
WHERE TheDate = (SELECT MAX(TheDate) FROM MyTable);