'gropu by' problem about SQL

[vbmask]

Hi~ Good nite every one

I have a problem about using 'group by' to query in MySQL 3.2x.

There is a table named as 'SETLOG' below.

select KeyinTime,Action,CID from SETLOG;
+---------------------+--------+------+
| KeyinTime | Action | CID |
+---------------------+--------+------+
| 2002-04-03 17:05:13 | C | 1 |
| 2002-04-03 17:05:13 | C | 2 |
| 2002-04-03 17:05:13 | C | 3 |
| 2002-04-03 17:05:13 | C | 4 |
| 2002-04-03 17:05:13 | C | 5 |
| 2002-04-03 17:05:13 | C | 6 |
| 2002-04-03 17:05:13 | C | 7 |
| 2002-04-03 17:05:13 | C | 8 |
| 2002-04-03 17:05:13 | C | 9 |
| 2002-04-10 14:23:37 | C | 10 |
| 2002-04-10 14:23:37 | C | 11 |
| 2002-04-10 14:23:37 | C | 12 |
| 2002-04-10 14:23:37 | C | 13 |
| 2002-04-10 14:31:23 | C | 14 |
| 2002-04-10 14:31:23 | C | 15 |
| 2002-04-10 14:31:23 | C | 16 |
| 2002-04-10 14:31:23 | C | 17 |
| 2002-04-11 12:06:53 | C | 18 |
| 2002-04-18 23:59:20 | S | 5 |
| 2002-04-18 23:59:20 | S | 4 |
| 2002-04-18 23:59:20 | S | 3 |
| 2002-04-18 23:59:20 | S | 2 |
| 2002-04-18 23:59:20 | S | 1 |
+---------------------+--------+------+
23 rows in set (0.01 sec)

I have to query the latest Action for every CID.

I tried the sql commands below, but failed.
(1)
select max(KeyinTime),Action,CID
from SETLOG
group by CID;

or

(2)
select KeyinTime,Action,CID
from SETLOG
group by CID
having KeyinTime=max(KeyinTime);

The results are the same:
+---------------------+--------+------+
| max(KeyinTime) | Action | CID |
+---------------------+--------+------+
| 2002-04-18 23:59:20 | C | 1 |-+
| 2002-04-18 23:59:20 | C | 2 | +
| 2002-04-18 23:59:20 | C | 3 | +=>why 'C' not 'S'?!
| 2002-04-18 23:59:20 | C | 4 | +
| 2002-04-18 23:59:20 | C | 5 |-+
| 2002-04-03 17:05:13 | C | 6 |
| 2002-04-03 17:05:13 | C | 7 |
| 2002-04-03 17:05:13 | C | 8 |
| 2002-04-03 17:05:13 | C | 9 |
| 2002-04-10 14:23:37 | C | 10 |
| 2002-04-10 14:23:37 | C | 11 |
| 2002-04-10 14:23:37 | C | 12 |
| 2002-04-10 14:23:37 | C | 13 |
| 2002-04-10 14:31:23 | C | 14 |
| 2002-04-10 14:31:23 | C | 15 |
| 2002-04-10 14:31:23 | C | 16 |
| 2002-04-10 14:31:23 | C | 17 |
| 2002-04-11 12:06:53 | C | 18 |
+---------------------+--------+------+
18 rows in set (0.01 sec)

Could any one help me? Thank you very much.

 

[PruSQLer]

See if this works for you:

select keyin_time, action, CID
from setlog s1
where keyin_time in (select max(keyin_time)
From setlog s2
Where s2.CID = s1.CID )