How to modify the view to set one field with min value of its field

From: mullin <mullin.yu_at_gmail.com>
Date: 1 Sep 2005 04:09:35 -0700
Message-ID: <1125572975.333719.230380@f14g2000cwb.googlegroups.com>

Now, after running the orginal view, i will have records with same db_tran_id and those may have different a.ADUIT_DATE. But, I want to make them have the min a.AUDIT_DATE for all records with same_tran_id when select from the view:

Anyone can help me for the following

Original View

---

CREATE OR REPLACE VIEW VW_RPT_AUDIT_ROOT_SYMBOL (COMM_CODE, USER_ID, AUDIT_DATE, AUDIT_TIME, TYPE,  DB_TRAN_ID, TOPIC, TOPIC_KEY, SUBTOPIC, SUBTOPIC_KEY,  DESCRIPTION, SEQ_NO, DATA_TYPE, DEFAULT_VALUE, PRECISION,  NEW_VALUE, OLD_VALUE)
AS
select det1.text_1 as business_key,

           u.user_id,
to_date(to_char(a.AUDIT_DATE,'YYYYMMDD'),'YYYYMMDD'), to_char(a.AUDIT_DATE,'HH24:MI:SS'), a.DB_ACTION, a.db_tran_id,

           a.topic, a.topic_key, a.subtopic, a.subtopic_key,
           fd.description, fd.seq_no, fd.data_type, default_value,

precision, det2.text_1 as value1, det2.text_2 as value2 from sds_audit_log a, scs_user u, sds_audit_log_detail det1, sds_audit_log_detail det2, sds_audit_rpt_field fd where a.topic = 'SDS_ROOT_SYMBOL'
and a.subtopic = 'SDS_ROOT_SYMBOL'
and a.id = det1.audit_id
and det1.KEYWORD = 'SDS_ROOT_SYMBOL'
and det1.FIELD_NAME = 'COMM_CODE'
and a.id = det2.audit_id
and a.update_user = u.id

and fd.topic = a.topic
and fd.subtopic = a.subtopic
and fd.keyword = det2.keyword
and fd.field_name = det2.field_name

and (det2.text_1 IS NOT NULL OR det2.text_2 IS NOT NULL)

Way to get the min of a.AUDIT_DATE and its db_tran_id

---

select min(a.audit_date), db_tran_id from sds_audit_log a where a.topic = 'SDS_ROOT_SYMBOL'
and a.subtopic = 'SDS_ROOT_SYMBOL'
group by a.db_tran_id Received on Thu Sep 01 2005 - 06:09:35 CDT