Abayob Abayob - 6 months ago 31
MySQL Question

Query max(date) with group by not working on SELECT ... IN?

Each page in my system has multiple page_objects.
I need to return the last_changed records of my page_objects per page.

To decrease DB-impact, I have a SELECT ... IN query to return each last-edited page_object per page:

SELECT object, f_page_id, page_object_id, last_change
FROM page_objects
WHERE f_page_id IN (page_id1, page_id2, page_id3, etc...) GROUP BY f_page_id
ORDER BY last_change ASC;


Of course this does not work, because
GROUP BY
is applied before
ORDER BY
, so I changed the query:

SELECT object, f_page_id, page_object_id, max(UNIX_TIMESTAMP(last_change))
FROM page_objects
WHERE f_page_id IN (page_id1, page_id2, page_id3, etc...) GROUP BY f_page_id


But this still does not return the last-edited
page_object
per page_id.

What am I doing wrong?

Answer

Your query does not specify to get the record for the latest last_change. Merely that it gets the latest value of last_change. The other non aggregate values (ie, not the result of an aggregate function like MAX or MIN) that are not mentioned in the GROUP BY clause can come from any row for the grouped values.

As such you use a sub query to get the latest value for each page, and then join that back against your main table to get the matching rows

Something like this:-

SELECT page_objects.object, 
        page_objects.f_page_id, 
        page_objects.page_object_id, 
        page_objects.last_change
FROM page_objects 
INNER JOIN 
(
    SELECT f_page_id, MAX(last_change) AS latest_last_change
    FROM page_objects 
    GROUP BY f_page_id
) sub0
ON page_objects.f_page_id = sub0.f_page_id
AND page_objects.last_change = sub0.latest_last_change
WHERE page_objects.f_page_id IN (page_id1, page_id2, page_id3, etc...) 
ORDER BY last_change DESC 

Note that MySQL is quite unusual at allowing you to have non aggregate columns that are not mentioned in the GROUP BY clause (as it is against SQL standards, except under very particular circumstances). Most flavours of SQL will issue an error if you try this, and MySQL has a configuration parameter which will similar cause it to reject such queries.

Comments