CODEWITHSUNDEEP
phpsqlmysql
Eric
Eric

Reputation: 119

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 once the mysql query is corrected Ill need it to count each employee up and display the results like so. 

Bill, 2

Upvotes: 1

Views: 420

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332521

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

Upvotes: 4

Josh Bedo
Josh Bedo

Reputation: 3462

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

Upvotes: 0

Related Questions