Delete all rows but the latest date

Question

I've got this table supp_props:

supp_id | supp_dry_weight | supp_price | supp_date
--------+-----------------+------------+------------ 
  22    |      88.00      |   27.50    | 2015-06-25   x delete
  22    |      89.00      |   28.00    | 2015-10-18       < don't delete, while
  22    |      89.00      |   29.50    | 2015-12-20         this row here is in the future
  23    |      84.00      |   15.00    | 2015-06-23   x delete
  23    |      42.50      |   7.50     | 2015-06-25   x delete
  23    |      35.60      |   5.00     | 2015-06-29
  24    |      89.00      |   18.20    | 2015-06-25
  25    |      89.15      |   18.50    | 2015-08-05
  26    |      89.00      |   28.30    | 2015-06-25

And I want to delete all rows that are 'old', but there are some conditions:

• a row must be older than two weeks to be deleted.
• if there is no row with a more current date it shouldn't be deleted.
• There may be rows that are in the future, if so, this row shouldn't be seen as a 'more current' date (for the above rule).

How can I achieve that with mysql?

Answer 1

Thanks to all the great help and examples from Alex, Mike Brent and xQbert I've managed to create this query:

DELETE sp 
FROM supp_props sp
LEFT JOIN (
   SELECT supp_id, MAX( supp_date ) AS max_date
   FROM supp_props
   WHERE supp_date < NOW()
   GROUP BY supp_id
) max
ON sp.supp_id = max.supp_id
WHERE sp.supp_date < ( DATE_SUB( CURDATE(), INTERVAL 2 week ) )
AND sp.supp_date <> max.max_date

Thank you all for your help!