select a row previous from the Max value.

Database
Enthusiast

select a row previous from the Max value.

Hello Friends,

The question is something like this

Get 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
4 REPLIES
Enthusiast

Re: select a row previous from the Max value.

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 table
group by 1;

select acct_id, max(end_dt)
from table where end_dt < VT.maxdate
group by 1;

Tbob
Enthusiast

Re: select a row previous from the Max value.

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 ))
Junior Contributor

Re: select a row previous from the Max value.

Easy using OLAP functions:

select *
from ...
qualify
rank() over (partition by acct_id order by plcmt_end_dt desc) = 2

Dieter
Enthusiast

Re: select a row previous from the Max value.


Hey

Thanks a lot. It worked.