mysql clock,MySQL的时钟

I have a timeclock table with the following elements (id(pk), action, emp_id(fk), time).

How can I select the latest action if I pass the emp_id to the query?

id emp_id action current_time

-----------------------------------------------

1 1 clockin 2012-01-29 21:52:38

2 1 clockout 2012-01-29 21:54:24

3 3 clock in 2012-01-30 21:10:51

4 4 clock in 2012-01-30 21:10:51

5 9 clock in 2012-01-30 21:11:05

6 10 clock in 2012-01-30 21:11:05

7 10 go to meal 2012-01-30 21:11:38

8 9 go to meal 2012-01-30 21:11:38

9 9 back 2012-01-30 21:12:53

10 3 back 2012-01-30 21:12:53

11 2 back 2012-01-30 21:12:53

12 1 back 2012-01-30 21:12:53

If I write my query like this:

SELECT MAX(ID) FROM timeclock WHERE emp_id = 1

I get 12 which is right but if i

SELECT MAX(ID) , action FROM timeclock WHERE emp_id = 1

I get

id->12 and action -> 'clockin' instead of 'back' which is definitely not right

解决方案

You're missing a GROUP BY clause for your MAX() aggregate. The reason you got the correct answer 12 for your first query attempt was merely because that happens to be the greatest ID in the table while also coincidentally belonging to emp_id = 1. You would have gotten the same result for any of the emp_id values. A GROUP BY clause will sort this out.

Here is an example retrieving the whole row for the associated record:

SELECT * FROM timeclock

WHERE id = (SELECT MAX(id) AS id FROM timeclock WHERE emp_id = 1 GROUP BY emp_id);

This can also be done with a HAVING clause, not needing the subquery:

SELECT action

FROM timeclock

WHERE emp_id = 1

GROUP BY emp_id

HAVING id = MAX(id);