Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query Quandry.
Rob van Wijk wrote:
> On 13 nov, 13:45, "Preston" <dontwant..._at_nowhere.invalid> wrote:
> > Anyone got a neat solution for this in 10.2.0.1?
>
> SQL> select hid
> 2 , t1
> 3 , t2
> 4 , t3
> 5 , t4
> 6 , value51 * ratio_to_report(mpc) over (partition by
> hid,t1,nvl2(maxt2,t2,1),nvl2(maxt3,t3,1)) mpc
> 7 from ( select t.*
> 8 , first_value(mpc) over (partition by
> t1,nvl2(maxt2,t2,1),nvl2(maxt3,t3,1) order by hid) value51
> 9 from ( select t.*
> 10 , max(decode(hid,51,t2)) over () maxt2
> 11 , max(decode(hid,51,t3)) over () maxt3
> 12 from mytable t
> 13 ) t
> 14 )
> 15 where hid = 53
> 16 order by id
> 17 /
Thanks Rob, you're a star. I'll pump some data in & see what happens.
-- Preston.Received on Tue Nov 13 2007 - 09:03:40 CST