SQL Problem about moving average

Hello, theres
[Quoted] [Quoted]   I have a question about getting Moving Average in Stock.   I have a table called cp ( ClosePrice ).   create table cp ( xday int, StockID char(4), LastTrade number(5,2))   (xday is the sequence)

  here are the sample data:
xday StockID LastTrade

1	2303	59.03
2	2303	47.51
3	2303	57.56
4	2303	85.43
5	2303	86.63
6	2303	78.84
7	2303	34.34
8	2303	20.54
9	2303	56.82
10	2303	58.04
11	2303	1.23
12	2303	82.98

I want to get the result like 
xday  StockID  LastTrade  MA6     (AVG(ClosePrice) between xday-5 and xday)
----------------------------------
1	2303	59.03	59.030000    
2	2303	47.51	53.270000
3	2303	57.56	54.700000
4	2303	85.43	62.382500
5	2303	86.63	67.232000
6	2303	78.84	69.166666   ( day 1,2..6's avg)
7	2303	34.34	65.051666   ( day 2,3..7's avg)
8	2303	20.54	60.556666
9	2303	56.82	60.433333
10	2303	58.04	55.868333
11	2303	1.23	41.635000
12	2303	82.98	42.325000   ( day 7-12's avg )

Can I write SQL statement to deal with this ??

It seems doesn't support the syntax that MSSQL does

select *, (select avg(LastTrade)

            from cp as a 
           where a.xday between b.xday-5 and b.xday
                      and a.stockID=b.stockID ) as MA6

Rgds,
Agi Chen Received on Mon Aug 06 2001 - 12:12:26 CEST