Should first_value() over (order by something asc) be the same as last_value () over (order by something desc)?

Question

Shouldn't first_value() ordered descending give the same results as last_value() ordered ascending? I ran the following query and got the results attached below the query.

select random_date, trunc(random_date,'MM') random_month, random_value,
first_value(random_value) over (partition by trunc(random_date,'MM') order by random_date desc) first_,
last_value(random_value) over (partition by trunc(random_date,'MM') order by random_date) last_
from 
(select to_date(round (dbms_random.value (1, 28))
        || '-'
        || round (dbms_random.value (02, 03))
        || '-'
        || round (dbms_random.value (2014, 2014)),
        'DD-MM-YYYY') + level - 1 random_date,
        round(100*(dbms_random.value)) random_value
from dual
connect by level <= 10) order by 2, 1;

Answer 1

You forgot about a moving window used for analytical functions. See the difference (used rows between unbounded preceding and unbounded following):

SQL> select random_date, trunc(random_date,'MM') random_month, random_value,
  2  first_value(random_value) over (partition by trunc(random_date,'MM') order by random_date desc rows between unbounded preceding and unbounded following) first_,
  3  last_value(random_value) over (partition by trunc(random_date,'MM') order by random_date rows between unbounded preceding and unbounded following) last_
  4  from
  5  (select to_date(round (dbms_random.value (1, 28))
  6          || '-'
  7          || round (dbms_random.value (02, 03))
  8          || '-'
  9          || round (dbms_random.value (2014, 2014)),
 10          'DD-MM-YYYY') + level - 1 random_date,
 11          round(100*(dbms_random.value)) random_value
 12  from dual
 13  connect by level <= 10) order by 2, 1;

RANDOM_DATE RANDOM_MONTH RANDOM_VALUE     FIRST_      LAST_
----------- ------------ ------------ ---------- ----------
02.02.2014  01.02.2014             93         75         75
09.02.2014  01.02.2014             78         75         75
11.02.2014  01.02.2014             69         75         75
12.02.2014  01.02.2014             13         75         75
21.02.2014  01.02.2014             91         75         75
25.02.2014  01.02.2014             75         75         75
01.03.2014  01.03.2014             54         80         80
15.03.2014  01.03.2014             37         80         80
16.03.2014  01.03.2014             92         80         80
17.03.2014  01.03.2014             80         80         80

10 rows selected