GROUP BY having MAX date

Question

I have problem when executing this code   SELECT   FROM tblpm n  WHERE date updated  SELECT MAX date updated   FROM tblpm GROUP BY control number  HAVING control number n control number    Basically  I want to return the most recent date for each control number  The query above returns correct output but it takes 37secs  before the output was shown   Is there any other sql clause or command that can execute faster than the query above   Thanks in advance

User · Accepted Answer

Putting the subquery in the WHERE clause and restricting it to n.control_number means it runs the subquery many times. This is called a correlated subquery, and it's often a performance killer.

It's better to run the subquery once, in the FROM clause, to get the max date per control number.

SELECT n.* 
FROM tblpm n 
INNER JOIN (
  SELECT control_number, MAX(date_updated) AS date_updated
  FROM tblpm GROUP BY control_number
) AS max USING (control_number, date_updated);

User · Answer

There s no need to group in that subquery    a where clause would suffice   SELECT   FROM tblpm n WHERE date updated  SELECT MAX date updated      FROM tblpm WHERE control number n control number    Also  do you have an index on the  date updated  column   That would certainly help

User · Answer

Fast and easy with HAVING   SELECT   FROM tblpm n  FROM tblpm GROUP BY control number  HAVING date updated MAX date updated     In the context of HAVING  MAX finds the max of each group  Only the latest entry in each group will satisfy date updated max date updated   If there s a tie for latest within a group  both will pass the HAVING filter  but GROUP BY means that only one will appear in the returned table

User · Answer

Another way that doesn t use group by   SELECT   FROM tblpm n    WHERE date updated  SELECT date updated FROM tblpm n                          ORDER BY date updated desc LIMIT 1

[mysql] GROUP BY having MAX date

Examples related to mysql

Examples related to sql

Examples related to optimization

Examples related to greatest-n-per-group