Use of OLAP function

[sandeep.iitk]

Hi Friends,
can anybody help me in rewriting below query with the help of OLAP
functions. I never used OLAP functions , so need help in this regard.

SELECT C.UNIT, C.DT from tab1 C where C.DT = (SELECT MAX(C_ED.DT) FROM
tab1 C_ED WHERE C.UNIT = C_ED.UNIT AND C.ID = C_ED.ID AND C_ED.DT <=
CURRENT DATE)

Thanks in advance
Sandy

[ChrisC]

On Nov 20, 12:02*pm, "sandeep.i...@g mail.com" <sandeep.i...@g mail.com>
wrote:

Hi Friends,
can anybody help me in rewriting below query with the help of OLAP
functions. I never used OLAP functions , so need help in this regard.

SELECT C.UNIT, C.DT from tab1 C where C.DT = (SELECT MAX(C_ED.DT) FROM
tab1 C_ED WHERE C.UNIT = C_ED.UNIT AND C.ID = C_ED.ID AND C_ED.DT <=
CURRENT DATE)

select unit, dt from (select unit, dt, max(dt) over(partition by unit,
id) as max_dt where dt <= current date) sub
where dt = max_dt

Note that some description behind what this query is supposed to do
would help in figuring out the right answer - this is just a guess
that should replicate what your query is returning.

Chris