Re: I thisk I got it! It is possible, even *with* a subquery!

[Erin ONeill]

Hey Roland -
I cut and pasted your query but I got some cross join thing where my 67 rows turned into something like 223??

I can get a subset of the data that is wanted for a temp table like this:

select @maxtime := max(running_time) from log_table;
select distinct member_name, ip_address,running_time from log_table where running_time = @maxtime;

There may still be duplicates of (member_name,ip_address) unless when you create the temp table you so with a unique index on (member_name,ip_address) then only ONE of those pairs will make it into the new table. Be sure you don't copy that unique index over to the real log table though.

I'm pretty sure you have to do INSERT IGNORE so that MySQL won't barf on the dups.


But I'm still working on a select to get all the rows that are less than this time AND all the dups of this time. After that it'll take some tweaking for performance. (hey where is Jay when you need him!)

But in thinking about this, and with the thought of millions of records in the table and only wanting to keep a small number of them, I think using a temp table, then truncating the old table and moving the small data set back from the temp table to the original table may be the fastest, more efficient way to go. IF the table were only to contain hundreds or thousands of records perhaps another way would be better? not sure.

Took me awhile to make the dummy data and to realize (this is why CREATE TABLE scripts are so great) that the running_time was unixtime and not datetime.

Oh I did this on a 4.1.17 server.

erin