subquery using max(field1) to get info on field2

[pnj]

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

[shoz]

[code]SELECT
v.*
FROM
visits AS v
INNER JOIN
(
    SELECT
    parent_id, MAX(vdate) AS vdate
    FROM
    visits AS v2
    GROUP BY
    parent_id
) AS vmax
ON v.parent_id = vmax.parent_id
AND v.vdate = vmax.vdate
[/code]

[pnj]

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]

Cheers
pnj

[fenway]

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.