Jump to content


subquery using max(field1) to get info on field2

  • Please log in to reply
3 replies to this topic

#1 pnj

  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts

Posted 13 September 2006 - 08:54 AM

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?



#2 shoz

  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 13 September 2006 - 10:44 AM

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

#3 pnj

  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts

Posted 19 October 2006 - 11:33 AM

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:
SELECT SUBSTR(MAX(CONCAT(vdate,answer)),11) FROM visit GROUP BY _parent_id;


#4 fenway

  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 October 2006 - 04:28 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users