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;

enter image description here

Maybe off topic: what is your Oracle version? This area of SQL used to be very buggy in early 11g releases. Analytic functions were evaluated in "wrong" order. — ibre5041, Mar 13 '15 at 08:57

score 3 · Accepted Answer · answered Mar 13 '15 at 08:51

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

thanks! Isn't this the default window for analytical functions? What is the default window? — MontyPython, Mar 13 '15 at 08:55
@MontyPython, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is default window. — Noel, Mar 13 '15 at 08:58

Stanislovas Kalašnikovas · Answer 2 · 2015-03-13T09:16:46.823

0

Try this Analytic Functions. To achieve your goald should use rows between unbounded preceding and unbounded.

first_value(random_value) over (partition by trunc(random_date,'MM') order by random_date desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_,
last_value(random_value) over (partition by trunc(random_date,'MM') order by random_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_

Full code:

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 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_,
last_value(random_value) over (partition by trunc(random_date,'MM') order by random_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 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;

edited Mar 13 '15 at 09:16

answered Mar 13 '15 at 08:52

Stanislovas Kalašnikovas

9,203
4
32
59

1

selecting from dual should be oracle? – swe Mar 13 '15 at 08:56
1

I don't think we can use 'dual' in SQL Server. Can we? – MontyPython Mar 13 '15 at 09:01
your link shows over in tsql. selecting from dual is oracle! – swe Mar 13 '15 at 09:03
Here is discussion about `dual` in tsql. https://ask.sqlservercentral.com/questions/37933/what-is-the-oracle-equivalent-of-dual-table-in-sql.html – Stanislovas Kalašnikovas Mar 13 '15 at 09:04
but additionally the op tagged his post with "oracle" – swe Mar 13 '15 at 09:11
But where is the problem? This solution which I gave not working in oracle? – Stanislovas Kalašnikovas Mar 13 '15 at 09:17
@Stanislovas Kalašnikovas - It is working fine in Oracle. The only problem was that you said that this will work for SQL Server 2012 and then you edited your answer - which makes it alright now! – MontyPython Mar 13 '15 at 12:56

score 0 · Answer 3 · answered Mar 13 '15 at 12:48

0

As I see you do first_value this is used only for strings but you need it for a number so change it to min and max In a string 10 will be before 2 but in number 2 is before

answered Mar 13 '15 at 12:48

Zvi W.

13
1
7

I did not want min and max. I actually wanted first_value and last_value. I am treating the numbers at strings. – MontyPython Mar 13 '15 at 12:55

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

3 Answers3