Vijay Vijay - 1 year ago 55
SQL Question

How to use MySql MAX() in a WHERE clause

I have one table named

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download