Jump to content


This topic is now archived and is closed to further replies.


subquery using max(field1) to get info on field2

Recommended Posts

I am having difficulty structuring a subquery using the MAX() operation.

I have a table 'visit' with the following fields:

primary_key, parent_id, vdate, answer

Each row is linked to a parent table via parent_id, and there can be multiple rows for each parent_id.  I want my query to give me the contents of the 'answer' field for the most recent date for each parent_id.

This select query gets the max visit date, but the answer field is not necessarily from the same row as the max visit date, so this is not the answer:

SELECT MAX(visit_date), answer FROM visit GROUP BY parent_id;

This subquery returns what appears to be the correct result, but takes mysql 2 minutes and 30 seconds to compute:

SELECT answer FROM visit WHERE (parent_id, vdate)=ANY (SELECT parent_id, MAX(vdate) FROM visit GROUP BY parent_id);

It doesn't seem like that complicated a request, especially since I am only talking about 2500 rows in the visit table, and 500 rows returned from the subquery.

Can someone recommend a more efficient approach?



Share this post

Link to post
Share on other sites
visits AS v
    parent_id, MAX(vdate) AS vdate
    visits AS v2
) AS vmax
ON v.parent_id = vmax.parent_id
AND v.vdate = vmax.vdate

Share this post

Link to post
Share on other sites
Thanks, Shoz.

The auto email seems not to have triggered so I did not see your reply until now.  Thanks for the solution, I had not thought of inner joining the table to a subset of itself, very nice.

I have since found the following alternate solution:
[code]SELECT SUBSTR(MAX(CONCAT(vdate,answer)),11) FROM visit GROUP BY _parent_id;[/code]


Share this post

Link to post
Share on other sites
That's quite the hack... and will only work under very particular instances; use the derived table solution provided by shoz instead -- it will serve you better in the long run.

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.