Finding Most recent record 1

[CARLEENHOP]

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 ;

 

[taupirho]

I can't see anything obviously wrong with your code. Can you post create table statements for your tables, insert statements to populate each with a few meaningful records and finally the exact outputs that SQLPLUS produces when your code does and does not work.


In order to understand recursion, you must first understand recursion.

 

[LKBrwnDBA]

You should format and wrap your code using the "code" tags.

Try something like this:

SELECT s.num,
       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
       AND al.AUD_DATE = (SELECT MAX (AUD_DATE)
                            FROM ap21log_audit a0
                           WHERE a0.logidx = a.logidx);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[CARLEENHOP]

Hi taupirho, thx for your response. I am conncecting to an oracle db using golden 32 if that helps.

Not sure how to do what you need and only have acces to query. Here are the results from that script, if that helps

NUM CHANGEDATE NEWDATE OLDDATE WHOCHANGED
29895 09/03/2010 2010-06-07 Joceline Patterson
29895 25/05/2010 2010-07-05 2010-06-07 Pat Karabasis
31597 03/08/2010 2010-07-30 Vivek Suri

 

[CARLEENHOP]

Hi LKBrwnDBA,

I did what you said but still get the same results as posted above.

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
AND al.AUD_DATE = (SELECT MAX (AUD_DATE)
FROM ap21log_audit a0
WHERE a0.logidx = a.logidx)

GROUP by s.num, al.new_value, al.old_value , al.new_value, m.memfull ;

 

[Dagon]

You don't need the MAX or GROUP BY any longer as the subquery is restricting it to the most recent record.

For Oracle-related work, contact me through Linked-In.

 

[CARLEENHOP]

Hi Dagon

Thanks, but I removed what you mentioned and still get the same results (3 records instead of 2). Any other ideas? See code below:

select
s.num,
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
AND al.AUD_DATE = (SELECT MAX (AUD_DATE)
FROM ap21log_audit a0
WHERE a0.logidx = a.logidx) ;

 

[Dagon]

It's a bit difficult without knowing what the structure of the tables is. What is the relationship between a21log and ap21log_audit? Is it 1-to-1 or 1-to-many?

For Oracle-related work, contact me through Linked-In.

 

[CARLEENHOP]

1:M

 

[Dagon]

The problem is that it seems to be s.num that you are trying to make unique rather than a.logidx. Something like this might be easier to get right:

SELECT num,
       changedate,
       newdate,
       olddate,
       whochanged
FROM
(
SELECT s.num,
       al.AUD_DATE AS changedate,
       al.new_value AS newdate,
       al.old_value AS olddate,
       m.memfull AS whochanged,
       rank() over (partition by s.num order by al.aud_date desc) as rn
  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)
where rn = 1

For Oracle-related work, contact me through Linked-In.

 

[CARLEENHOP]

Thanks it worked. Now I just need to get it working in my bigger query. Really appreciate your help.

 

[CARLEENHOP]

Hi, I am trying to get this code working in another query and not having much luck. I tried to embed within existing query. I think I just need help with the structure.

select
so.ofrom,
o.sord_sordidx,
o.sord_num,
o.cust_code,
s.style_stylecode,
o.style_ref1_c as Brand,
o.style_ref2_c as Dept,
o.style_ref3_c as ProdGroup,
o.style_ref4_c as busgroup,
o.style_ref5_c as AccManag,
o.style_ref9_c as season,
o.style_ref10_c as yr,
st.cost as budgetcost,
st.estcost as budsell,
sp.price as rrp,
st.margin as fm,
o.sord_gendate,
o.sord_duef,
o.sord_cand,
a.log_date as changedate,
al.old_value as olddate,
al.new_value as newdate,
m.memfull as whochanged,

o.sozd_orig_1,
o.sozd_invq_1,
o.sozd_compl_qty_1,
s.sord_num,
s.sord_gldate,
s.sord_chad,
s.sord_despdate,
s.sozd_outst_qty_1,
s.sozd_fgross_1,
s.sozd_fnet_1,
s.sozd_fdisc_1,
s.sozd_tcost_1,
bs.code as bomcode,
si.code as supplier,
bom.fcost as unitforeign,
bom.cost as unitlocal

from (

select
so.ofrom,
o.sord_sordidx,
o.sord_num,
o.cust_code,
s.style_stylecode,
o.style_ref1_c as Brand,
o.style_ref2_c as Dept,
o.style_ref3_c as ProdGroup,
o.style_ref4_c as busgroup,
o.style_ref5_c as AccManag,
o.style_ref9_c as season,
o.style_ref10_c as yr,
st.cost as budgetcost,
st.estcost as budsell,
sp.price as rrp,
st.margin as fm,
o.sord_gendate,
o.sord_duef,
o.sord_cand,
a.log_date as changedate,
al.old_value as olddate,
al.new_value as newdate,
m.memfull as whochanged,
o.sozd_orig_1,
o.sozd_invq_1,
o.sozd_compl_qty_1,
s.sord_num,
s.sord_gldate,
s.sord_chad,
s.sord_despdate,
s.sozd_outst_qty_1,
s.sozd_fgross_1,
s.sozd_fnet_1,
s.sozd_fdisc_1,
s.sozd_tcost_1,
bs.code as bomcode,
si.code as supplier,
bom.fcost as unitforeign,
bom.cost as unitlocal,
rank() over (partition by o.sord_num order by al.aud_date desc) as rn

FROM
SA_DAVID_ORDERSCURRENT o,
SA_DAVID_SALESCURRENT s,
sord so,
styles st,
sprice_style sp,
bom_style bs,
supplier si,
bomtbl bom,
ap21log a,
ap21log_audit al,
member m ,
refcode r,
sord ord

WHERE

o.sord_sordidx (+)= so.ofrom
and so.sordidx = s.sord_sordidx
--and so.ofrom(+) = o.sord_sordidx(+)
and o.style_styleidx = st.styleidx (+)
and s.style_styleidx = st.styleidx
and o.style_styleidx = sp.styleidx (+)
and sp.rcidx(+) ='40857'
--and o.style_ref1_c = 'ACCESSORIES'
and so.active = 1
--and o.clr_clrcode = s.clr_clrcode
--and o.sord_num = 24680
and st.DEF_BSIDX = bs.bsidx
--and i.bscsidx = st.DEF_BSIDX
--and i.styleidx(+) = st.styleidx
--and id.icidx = i.icidx
and bom.suppidx= si.supidx
and bs.active = 1
and bom.styleidx = st.styleidx
and bom.bsidx = bs.bsidx
and bom.active = 1
and pbomidx = 0
and compcode = 'O/S SUPPLY'

and a.fromidx = ord.sordidx (+)
and o.sord_sordidx = ord.sordidx
and a.memidx = m.memidx
and a.typeidx = r.rcidx
and r.rcidx = 622
and a.logidx (+) = al.logidx


group by
so.ofrom,
o.sord_sordidx,
o.sord_num,
o.cust_code,
s.style_stylecode,
o.style_ref1_c ,
o.style_ref2_c ,
o.style_ref3_c ,
o.style_ref4_c ,
o.style_ref5_c ,
o.style_ref9_c ,
o.style_ref10_c,
st.cost,
st.estcost,
sp.price,
st.margin,
o.sord_gendate,
o.sord_duef,
o.sord_cand,
a.log_date,
al.old_value,
al.new_value,
m.memfull,
o.sozd_orig_1,
o.sozd_invq_1,
o.sozd_compl_qty_1,
s.sord_num,
s.sord_gldate,
s.sord_chad,
s.sord_despdate,
s.sozd_outst_qty_1,
s.sozd_fgross_1,
s.sozd_fnet_1,
s.sozd_fdisc_1,
s.sozd_tcost_1,
bs.code,
si.code,
bom.fcost
bom.cost
)
-- and al.old_value is not null
--and o.sord_num = '29895'

where rn = 1;

 

[Dagon]

Why are you using a group by when you don't have any aggregate functions (max, min, count etc)? Ranking functions are themselves aggregate functions so you wouldn't normally use them in conjunction with group by.

For Oracle-related work, contact me through Linked-In.