MYSQL Query most recent entry

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 on开发者_开发问答ce the mysql query is corrected Ill need it to count each employee up and display the results like so.

Bill, 2

---

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 != ''

---

You have to add "order by id desc limit 0,1" to your query or you can try to mysql_last_insert_id