Hi!
I've got a problem with analitic functions (I'm newbie in this topic).
I have a table
gpw_notowania which have colums:
not_open,
not_minimum,
not_maximum,
not_close,
not_volume,
not_sp_id and
not_date.
I need to receive from database the information:
what is the open, minimum, maximum, close and sum of volume in every week? I have tried the code below but it tells me: ORA-01791 (marking not_date in ORDER clause).
Help me, please.
SELECT distinct
FIRST_VALUE(not_open) OVER (partition by to_char(not_date,'WW') ORDER BY not_date) as open,
MIN(not_minimum) OVER (partition by to_char(not_date,'WW') ORDER BY not_date) as minimum,
MAX(not_maximum) OVER (partition by to_char(not_date,'WW') ORDER BY not_date) as maximum,
FIRST_VALUE(not_close) OVER (partition by to_char(not_date,'WW') ORDER BY not_date DESC) as close,
sum(not_volume) OVER (partition by to_char(not_date,'WW')) as volume
FROM gpw_notowania
WHERE not_sp_id = 80
ORDER BY not_date;