MYSQL查询最近的条目

Question

Here is the problem. I need to transform this query to get the right information and Im not sure how.

Database info

   Id    |   Client_Code   |  Time_Date          | Employee_Name | Date_Time | Time_Stamp  
    1    |    000010       |  2010-11-17 07:45:00| Jerry         | 2010-11-17| 07:45 AM
    2    |    000022       |  2010-11-17 07:30:00| Jerry         | 2010-11-17| 07:30 AM
    3    |    000010       |  2010-11-17 16:00:00| Bill          | 2010-11-17| 04:00 PM
    4    |    000022       |  2010-11-17 16:00:00| Bill          | 2010-11-17| 04:00 PM

Here is the query

$sql = "SELECT Client_Code, MAX(TIME(Time_Date)), Employee_Name, Date_Time, Time_Stamp FROM Transaction WHERE Date_Time = CURdate()
                    AND Time_Stamp != '' GROUP BY Client_Code"; 

Here is what i get with this query and phpcode

echo " $row[Employee_Name], $row[Client_Code], ".$row['MAX(TIME(Time_Date))']."<br>";
Jerry, 000010, 16:00:00
Bill, 000022, 16:00:00

For some reason yes its giving me the right 16:00:00 Time but it is not giving me the right employee name with that time. It has something to do with the grouping I think but the group has to be by client_code first because I want the most recent entry for each Client_Code. Also I can not use ID for grouping because inputs are not always in order. Here is what is should look like.

Bill, 000010, 16:00:00
Bill, 000022, 16:00:00

Can anyone tell me how to fix this query to get the correct information please. Also once the mysql query is corrected Ill need it to count each employee up and display the results like so.

Bill, 2

Answer 1

Use a self join:

SELECT x.client_code,
       TIME(x.time_date)
       x.employee_name,
       x.date_time,
       x.time_stamp
  FROM TRANSACTION x
  JOIN (SELECT t.client_code,
               MAX(t.time_date) AS max_time_date
          FROM TRANSACTION t
         WHERE t.date_time = CURRENT_DATE
           AND t.time_stamp != ''
      GROUP BY t.client_code) y ON y.client_code = x.client_code
                               AND y.max_time_date = x.time_date
 WHERE x.date_time = CURRENT_DATE
   AND x.time_stamp != ''