I have a base table with the 2 columns, IMSI and Date. I would like to
calculate a moving average of distinct IMSIs in a given window. I have
developed my query to this point but am receiving an "ORDER BY not
allowed here" error when I try to execute the query.

select ir_date, count(distinct imsi) OVER (ORDER BY ir_date ASC RANGE 5
PRECEDING)
from ir_daily_unique
where ir_date between to_date('03-AUG-06','DD-MON-YY') and
to_date('15-AUG-06','DD-MON-YY')
group by ir_date

asif.shariff@gmail.com wrote:
> I have a base table with the 2 columns, IMSI and Date. I would like to
> calculate a moving average of distinct IMSIs in a given window. I have
> developed my query to this point but am receiving an "ORDER BY not
> allowed here" error when I try to execute the query.
>
> select ir_date, count(distinct imsi) OVER (ORDER BY ir_date ASC RANGE 5
> PRECEDING)
> from ir_daily_unique
> where ir_date between to_date('03-AUG-06','DD-MON-YY') and
> to_date('15-AUG-06','DD-MON-YY')
> group by ir_date

Does moving the order by next to the group by do what you want?

http://download-west.oracle.com/docs...2.htm#i2066419

More examples at
http://asktom.oracle.com/pls/ask/f?p...12864646978683

jg
--
@home.com is bogus.
http://www.explodingcigar.com/