SELECT MAX(MAXTC.CASE_ID) CASE_ID, BSM.REPLAY_SEQUENCE, INS.INSTANCE_ID FROM T_GPS_INSTANCES INS, T_GPS_APPLICATIONS APP, TRI2GPS_DEV.TEST_CASES TC, TRI2GPS_DEV.TEST_CASES MAXTC, TRI2GPS_DEV.BSM_MAIN BSM WHERE APP.APPLICATION_ID = INS.APPLICATION_ID AND TC.PROJECT_NAME = APP.APPLICATION_NAME AND TC.BSM_NUMBER = MAXTC.BSM_NUMBER AND MAXTC.BSM_NUMBER = BSM.BSM_NUMBER AND (bsm.active_flag is null or bsm.active_flag = 'true') --AND ins.instance_id=235536 group by bsm.replay_sequence,ins.instance_id /* Modified below query select max(maxtc.case_id) case_id,bsm.replay_sequence, ins1.instance_id instance_id from t_instances ins1, t_instances ins2, t_gps_applications app, v_instance_props prop, tri2gps_dev.links l, tri2gps_dev.issues iss, tri2gps_dev.test_cases tc, tri2gps_dev.test_Cases maxtc, tri2gps_dev.bsm_main bsm where ins1.application_id = app.application_id and prop.INSTANCE_ID = ins2.instance_id and ins2.application_id = app.application_id and l.reference_type like 'TC' and l.link_type = 'ISS' and iss.issue_id = l.entity_id and tc.case_id = l.reference_identifier and maxtc.bsm_number = tc.bsm_number and iss.issue_number = prop.IA_ISSUE_NUMBER and maxtc.bsm_number = bsm.bsm_number and (bsm.active_flag is null or bsm.active_flag = 'true') --and ins1.instance_id = 132311 group by bsm.replay_sequence,maxtc.bsm_number,ins1.instance_id */