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.
SQL Server MAX() with GROUP BY clause example First, the GROUP BY clause divided the products into groups by the brand names. Then, the MAX() function is applied to each group to return the highest list price for each brand.
MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).
MAX() function will give you the maximum values from all the values in a column. MAX function works with “date” data types as well and it will return the maximum or the latest date from the table.
MAX() function with HavingThe SQL HAVING CLAUSE is reserved for aggregate function. The usage of WHERE clause along with SQL MAX() have also described in this page. The SQL IN OPERATOR which checks a value within a set of values and retrieve the rows from the table can also be used with MAX function.
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);
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With