Re: How do I delete all but most recent record?

[Felix Geerinckx]

dump3 wrote:

> However, I'm now a bit CONFUSED on the syntax and what would be the best way to select out
> just the lastest (running_time) unique records where there
> is a distinct member_name AND ip_address field in the record.
>
> I'm clear on deleting the tables, can someone help with queries that would select out the only the
> latest based on running_time and copy to each the tables? I'll have log_table and log_table_hold
> as the empty temporary table. Each table will have the following fields:
>
> member_name
> member_id
> ip_address
> running_time
> session_id
> member_group
>
> The main thing is, we want to keep the latest log of where there is a unique member_name and
> ip_address pair.

The problem lies in the fact that you want to include the member_id, session_id and member_group into your temporary result, but only want to look at unique member_name and ip_address combinations. I can imagine that member_name and member_group are uniquely determined by member_id, so those are not the problem, but what about session_id?

If you do

SELECT
member_name,
member_id,
ip_address,
max(running_time) as running_time,
session_id,
member_group
FROM log_table
GROUP BY member_name, member_id, member_group, ip_address

you will (probably) get a wrong session_id (unless it also uniquely depends on one of a combination of the columns in the GROUP BY).
See http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html

You can solve this as explained here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html

but this will add complexity to you query, probably leading to performance issues.

If you don't care about the session_id, dumping the above select in your log_table_hold should be a matter of a few seconds with:

CREATE TEMPORARY TABLE log_table_hold;
SELECT
member_name,
member_id,
ip_address,
max(running_time) as running_time,
session_id,
member_group
FROM log_table
GROUP BY member_name, member_id, member_group, ip_address;

TRUNCATE log_table;
INSERT INTO log_table SELECT * FROM log_table_hold;
DROP TABLE log_table_hold;

--
felix
Please use BBCode to format your messages in this forum.