I'm trying to return the row with the MAX date but the MAX funciton doesn't appear to be working. Below is the code I'm trying to run along with the output: Any help you can provide will be very much appreciated.
select doc_prfx, doc_num, BKD_WGT, BKD_VOL, ACCEPTED_PCS, ACCEPTED_VOL, sum (ACCEPTED_WGT - BKD_WGT), sum (ACCEPTED_VOL - BKD_VOL), MAX(modified_date)from (Select distinct c.car_id, b.doc_prfx, b.doc_num, f.wt_bfr_awb as BKD_WGT, f.vol_bfr_awb as BKD_VOL,g.num_pcs as ACCEPTED_PCS,g.ttl_wt as ACCEPTED_WGT, g.vol as ACCEPTED_VOL,to_char(f.modified_date, 'DD-MON-YYYY HH24:MI:SS') as modified_datefrom sbh_res_itnry a, sbh_car b, sbh_res c, sbh_res_cust d, fwb_res_data_aud e, sbh_res_aud f, sbh_AWB gwhere c.car_id = b.car_id (+)and c.car_id = a.car_id and c.car_id = d.car_idand b.car_id = e.car_id (+)and c.car_id = f.car_id and c.car_id = g.car_idand a.aln_code = 'VS'and a.flt_num = '0003'and trunc (a.dep_date) = '04-APR-2017'--and e.rmk_typ = 'BKL' --in (null,'BKL')AND a.cncl_ind = 'N'and d.cust_typ = 'AGT'and b.cncl_ind = 'N'and c.enq_ind = 'N'and f.wt_bfr_awb is not nulland b.doc_prfx = '932'and b.doc_num in ('51493422'))group by doc_prfx, doc_num, BKD_WGT, BKD_VOL, ACCEPTED_PCS, ACCEPTED_VOL,modified_date
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
Open in new window
Depending on the problem, the subquery must be correlated.