Exclude current row from being computed

  Hi,

Is there an easy way in SQL to exclude the current row from being computed when calculating an aggregate? The SQL ANSI window function has the EXCLUDE CURRENT ROW clause, but SQL Oracle does not.

Here is an example. I have 2 columns, and I want to compute the AVG of col2 partition by col1 excluding the current row:

COL1 COL2 AVG(COL2)
1         2          2
1         1          2,5
1         3          1,5

2         3         1,5
2         2          2
2        1          2,5

3        2          3
3       5          1,5
3       1          3,5

4       1         3,5
4       3         2,5
4       4         2

So for col1 = 1

record 1: avg(col2) = avg(col2) for record 2 and record 3

record 2: avg(col2) =  avg(col2) for record 1 and record 3

record 3: avg(col2) = avg(col2) for record 1 and record 2

And so on for other values of col1.