CARLEENHOP
MIS
- Mar 7, 2010
- 61
Hi, I have a few tables of which I am extracting data. It is an audit table. For each sales order I want to see the most recent change. I can currently see all changes for the order but not the most recent. I have tried to use the max function but this only works if I only select the order # and the date used in the max function. When I select the other fields the MAX function doesnt appear to work and displays all records. My code is below. I have googled andv viewed many websites and cant work it out. This is how I found the max feature though so it got me part way there. The satement below is the one which doesnt work, but if I only select the first two fields it does work.
ANy help is appreacited. Thanks
select s.num, max(al.AUD_DATE) as changedate, al.new_value as newdate, al.old_value as olddate,
m.memfull as whochanged
from sord s, ap21log a, ap21log_audit al, member m , refcode r
where s.num in ('31597','29895')
and s.typ = 1
and a.fromidx = s.sordidx
and a.memidx = m.memidx
and a.typeidx = r.rcidx
and r.rcidx = 622
and a.logidx = al.logidx
GROUP by s.num, al.new_value, al.old_value , al.new_value, m.memfull ;
ANy help is appreacited. Thanks
select s.num, max(al.AUD_DATE) as changedate, al.new_value as newdate, al.old_value as olddate,
m.memfull as whochanged
from sord s, ap21log a, ap21log_audit al, member m , refcode r
where s.num in ('31597','29895')
and s.typ = 1
and a.fromidx = s.sordidx
and a.memidx = m.memidx
and a.typeidx = r.rcidx
and r.rcidx = 622
and a.logidx = al.logidx
GROUP by s.num, al.new_value, al.old_value , al.new_value, m.memfull ;