If this should be posted in another forum, please let me know.

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?

Thanks,

Paul

Does doing a join help?

--
Don't Stand on your head - you'll get footprints in your hair
                                           http://charlieharvey.org.uk
                                              http://charlieharvey.com