Vijay Vijay - 7 months ago 12
SQL Question

How to use MySql MAX() in a WHERE clause

I have one table named

tbservicecallallocation
and below is data for that table.

enter image description here

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

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

Answer

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