I have a query where I would like to manipulate a column to return an array conatining only the max-value from the last 12 values from another column (due to other parts of the query).
Example: I want to add the column MaxLast12 from:
Month Power
1 10
2 16
3 8
4 14
5 15
6 3
7 6
8 10
9 11
10 12
11 12
12 12
13 18
14 12
To become:
Month Power MaxLast12
1 10 10
2 16 16
3 8 16
4 14 16
5 15 16
6 3 16
7 6 16
8 17 17
9 11 17
10 12 17
11 12 17
12 12 17
13 18 18
14 12 18
It would also help to be able to create a simpler solution where I only include the 12 rows in the query (won't be as accurate but good enough for the purpose) with only the maximum value. Would need to do the following:
Month Power
1 6
2 6
3 8
4 14
5 15
6 3
7 6
8 10
9 11
10 12
11 12
12 12
To become:
Month Power YearMax
1 10 17
2 16 17
3 8 17
4 14 17
5 15 17
6 3 17
7 6 17
8 17 17
9 11 17
10 12 17
11 12 17
12 12 17
Since I'm guessing both problems solution will be similar, any help possible is appriciated. Would like to avoid usign GROUP BY clause since I'm modifying an existing kind of complex query.
Tried to achive this using max() with no luck.
I am using SQL-developer.
In Oracle you would use window functions:
select month, power,
max(power) over (order by month rows between 11 preceding and current row)
from t;