That give some lights. Thank you On 9/13/07, Shamsudeen, Riyaj <RS2273@xxxxxxx> wrote: > > Make sure logic is right, by testing with many conditions though. > Last_value is to get last value in an ordered set and lag is to get previous > row in an ordered set. > > > > select * from ( > > select cod_ejer, id_comp, max_val as val, versioning, max_val - > lag( max_val ) over ( order by cod_ejer ) diff > > from ( > > SELECT distinct COD_EJER, ID_COMP, > > last_value( versioning ) > > over (partition by cod_ejer order by versioning rows between unbounded > preceding and unbounded following) versioning, > > last_value( val ) > > over (partition by cod_ejer order by versioning rows between unbounded > preceding and unbounded following) max_val > > FROM COMP_CL > > WHERE COD_EJER IN (200202, 200203) > > AND ID_COMP = 1173 > > ) > > ) where diff is not null > > / > > > > COD_EJER ID_COMP VAL VERSIONING DIFF > > ---------- ---------- ---------- ---------- ---------- > > 200203 1173 30000 3 7000 > > > ------------------------------ > > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *amonte > *Sent:* Thursday, September 13, 2007 4:34 AM > *To:* oracle-l > *Subject:* Analytics windowing wuth lag/lead? > > > > Hi all > > I have a query which returns following results > > SELECT > COD_EJER, > ID_COMP, > VAL, > VERSIONING > FROM COMP_CL > WHERE COD_EJER IN (200202, 200203) > AND ID_COMP = 1173 > AND KEY = 56 > > COD_EJER ID_COMP VAL VERSIONING > ------------ ---------- ---------- ----------- > 200202 1173 22000 1 > 200202 1173 23000 2 > 200203 1173 30000 1 > 200203 1173 30000 3 > > And teh requirement is: > > VAL OF MAX(VERSIONING) of 200203 - VAL OF MAX(VERSIONING) of 200202 > > So it would return folloging > > COD_EJER ID_COMP VAL VERSIONING DIFF > ------------ ---------- ---------- ----------- ----- > 200203 1173 30000 3 7000 > > I know I can do first a MAX(VERSIONING) then filter the max version rows > and use lag to subtract but I wonder if there are any better ways such as > using windowing? > > I am basically treating quarters (200201, 200202, 200203 200204) > > Cheers > > Alex > > >