Complex delete query

[cerv]

Hi,

I'm having problems trying to construct a delete query from a table. Below is a scaled down version of the table

host user date id
-----------------------------
mars john 27-6 1
pluto dave 27-6 2
venus jane 27-6 3
mars john 28-6 4
saturn tom 28-6 5
pluto dave 29-6 6


What I want to do is purge old records from the table. If I use the query:

SELECT max(date), host FROM table GROUP BY host;

(or I could even replace max(date) with max(id) )

Then I can get the latest record produced for each host. So basically I want to remove all records *other than* the latest records. If MySQL supported sub queries then this would be easy, but that's not the case yet. Anyone got any ideas?

Is this something I'm just going to have to do client side? I figured I could run the query above to get all id's of records to keep and then form a long string of them i.e.

keepString = "(1, 4, 7, 9, 12, 15, 17 etc.)"

and then run ("DELETE FROM table WHERE id NOT IN" + keepString) .

This seems a bit messy tho, and keepstring would contain over 100 id's if used.

All ideas greatly appreciated