subquery using max(field1) to get info on field2
Posted 13 September 2006 - 08:54 AM
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?
Posted 13 September 2006 - 10:44 AM
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
Posted 19 October 2006 - 11:33 AM
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;
Posted 19 October 2006 - 04:28 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users