All Forums Database
Kavya2007 12 posts Joined 09/06
12 Oct 2006
select a row previous from the Max value.

Hello Friends, The question is something like thisGet the previous home_router_code for all the accounts which is not current.So I need to select the Max(plcmt_end_dt) from all the accounts and go to the 2nd maximum plcmt_end_dt and then display Home_router_code based on 2nd maximum plcmt_end_dt. Thanks

RGlass 35 posts Joined 09/04
12 Oct 2006

Kayava,something like this should work: create volatile table VT acct_id char(10), maxdate date)on commit preserve rows.insert into VT select acct_id, max(end_date)from tablegroup by 1;select acct_id, max(end_dt)from table where end_dt < VT.maxdategroup by 1;Tbob

SarathyG 31 posts Joined 09/06
13 Oct 2006

here is a alternate approach which doesnt use any volatile tables but a subquery.select Home_router_code from where plcmt_end_dt = (select max(plcmt_end_dt) from T where T.plcmnt_end_dt NE (select max(plcmt_end_dt) from ))

- Sarathy G

dnoeth 4628 posts Joined 11/04
13 Oct 2006

Easy using OLAP functions:select * from ...qualify rank() over (partition by acct_id order by plcmt_end_dt desc) = 2Dieter

Dieter

Kavya2007 12 posts Joined 09/06
13 Oct 2006

Hey Thanks a lot. It worked.

You must sign in to leave a comment.