troubleshooting Question

SQL query to select row with MAX date

Avatar of Jason Palmer
Jason Palmer asked on
Oracle DatabaseSQL
7 Comments4 Solutions6498 ViewsLast Modified:
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 outputMax-query.JPG: 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
ASKER CERTIFIED SOLUTION
johnsone
Senior Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros