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

[Roland Bouman]

Lemme get this straight...

SELECT t1.member_name
, t1.ip_address
, t1.running_time
from log_table t1
inner join log_table t2
on t1.member_name = t2.member_name
and t1.ip_address = t2.ip_address
and t1.running_time < t2.running_time

yields the records to keep? It should return the records to delete. Your aim was to remove all but the latest record, yes?

here's my sample data:

mysql> select * from log_table;
+-------------+------------+---------------------+
| member_name | ip_address | running_time |
+-------------+------------+---------------------+
| a | 1.1.1.1 | 2005-07-27 21:08:23 |
| a | 1.1.1.1 | 2005-07-27 21:08:26 |
| a | 1.1.1.1 | 2005-07-27 21:08:28 |
| r | 1.1.1.1 | 2005-07-27 21:08:32 |
| r | 1.1.1.1 | 2005-07-27 21:08:35 |
| a | 1.1.1.1 | 2005-07-27 21:08:38 |
| a | 1.1.1.2 | 2005-07-27 21:08:42 |
+-------------+------------+---------------------+
7 rows in set (0.00 sec)

and here's the select query result:

mysql> SELECT t1.member_name
-> , t1.ip_address
-> , t1.running_time
-> from log_table t1
-> inner join log_table t2
-> on t1.member_name = t2.member_name
-> and t1.ip_address = t2.ip_address
-> and t1.running_time < t2.running_time
-> ;
+-------------+------------+---------------------+
| member_name | ip_address | running_time |
+-------------+------------+---------------------+
| a | 1.1.1.1 | 2005-07-27 21:08:23 |
| a | 1.1.1.1 | 2005-07-27 21:08:23 |
| a | 1.1.1.1 | 2005-07-27 21:08:26 |
| r | 1.1.1.1 | 2005-07-27 21:08:32 |
| a | 1.1.1.1 | 2005-07-27 21:08:23 |
| a | 1.1.1.1 | 2005-07-27 21:08:26 |
| a | 1.1.1.1 | 2005-07-27 21:08:28 |
+-------------+------------+---------------------+
7 rows in set (0.00 sec)

the remaining (latest) rows (those you want to retain) are:

| r | 1.1.1.1 | 2005-07-27 21:08:35 |
| a | 1.1.1.1 | 2005-07-27 21:08:38 |
| a | 1.1.1.2 | 2005-07-27 21:08:42 |