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 for each host
from the table. If I use the query:
SELECT max(date), host FROM table GROUP BY host;
Then I can get the latest record produced for each
host. There are never two records for a host produced
on the same date. So basically I want to remove all
records *other than* the latest records. If MySQL
supported sub queries then this would be easy:
DELETE from table where id not in (SELECT max(date)
FROM table GROUP BY host)
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 date NOT IN" +
keepString) .
This seems a bit messy tho, and keepstring would
contain over 100 id's if used.
All ideas greatly appreciated
__________________________________________________
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
