Jason Palmer
asked on
SQL query to select row with MAX date
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_date
from 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 g
where c.car_id = b.car_id (+)
and c.car_id = a.car_id
and c.car_id = d.car_id
and b.car_id = e.car_id (+)
and c.car_id = f.car_id
and c.car_id = g.car_id
and 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 null
and 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Caveat: Test always both approaches. Cause ROW_NUMBER() is not necessarily always better.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for you help guys
Open in new window
Depending on the problem, the subquery must be correlated.