How to use MySql MAX() in a WHERE clause

Question

I have one table named tbservicecallallocation and below is data for that table.

From above data i want that of technician with their MAX AllocationTime. Below image shows what result i want..

Please help me to write MySQL SELECT query for retrieve above data.

Answer 1

Have a sub-query to return each technician's max AllocationTime. Join with that result:

select t1.*
from tbservicecallallocation t1
join (select TechnicianIDF, max(AllocationTime) as MAxAllocationTime
      from tbservicecallallocation
      group by TechnicianIDF) t2
  on  t1.TechnicianIDF = t2.TechnicianIDF
  and t1.AllocationTime = t2.MAxAllocationTime

Optionally add ORDER BY clause at the end:

ORDER BY AllocationStatus DESC