Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Location
    Bangalore
    Posts
    15

    Unanswered: help with a query

    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 !

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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;
    rws

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •