Hi,

I am joining two tables bar_action and bar_object to obtain the
most recent failure record for any object .

SELECT obj_name, obj_type, obj_oid, MAX(act_aid)
FROM bar_object, bar_action
WHERE obj_oid = act_oid
AND act_status = 0
GROUP BY obj_name, obj_type, obj_oid
ORDER BY 4 DESC, 1, 2 DESC


I'd like to obtain the the value in another column of the bar_action table namely act_end. But I don't seem to be able to do it right.


How can I correctly obtain the act_end value corresponding to the record that has the max(act_aid) for each object.

Thanks a lot !

Why not use a temp table:

SELECT obj_name, obj_type, obj_oid, MAX(act_aid) as max_act_aid
FROM bar_object, bar_action
WHERE obj_oid = act_oid
AND act_status = 0
GROUP BY obj_name, obj_type, obj_oid
INTO TEMP obj_act_temp WITH NO LOG;

SELECT obj_name, obj_type, obj_oid, m_act_aid, act_end
FROM obj_act_temp, bar_action
WHERE max_act_aid = act_aid;

DROP table obj_act_temp;