Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle LAST_VALUE only with order by in analytic clause

591fce7a-eb89-4356-bd43-4a8e10a715dcMar 10 2019 — edited Mar 10 2019

Hi all i want to ask question regarding LAST_VALUE analytic function. I have schema (Oracle 11g R2):

    CREATE TABLE users (

      id INT NOT NULL,

      name VARCHAR(30) NOT NULL,

      num int NOT NULL

    );

    

    INSERT INTO users (id, name, num) VALUES (1,'alan',5);

    INSERT INTO users (id, name, num) VALUES (2,'alan',4);

    INSERT INTO users (id, name, num) VALUES (3,'julia',10);

    INSERT INTO users (id, name, num) VALUES (4,'maros',77);

    INSERT INTO users (id, name, num) VALUES (5,'alan',1);

    INSERT INTO users (id, name, num) VALUES (6,'maros',14);

    INSERT INTO users (id, name, num) VALUES (7,'fero',1);

    INSERT INTO users (id, name, num) VALUES (8,'matej',8);

    INSERT INTO users (id, name, num) VALUES (9,'maros',55);

And i execute following queries - using `LAST_VALUE` analytic function only with `ORDER BY` analytic clause :

My assumption is that this query executes over one partition - whole table (as partition by clause is missing). It will sort rows by name in given partition (whole table) and it will use default windowing clause `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.

    select us.*,  last_value(num) over (order by name) as lv from users us;

But the query executed above will give exactly same results as following one. My assumption concerning second query is that this query firstly partition table rows by name then sort rows in every partition by num and then apply windowing clause `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` over each partition to get `LAST_VALUE`.

    select us.*,  last_value(num) over (partition by name order by num RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lv  from users us;

One of my assumption is clearly wrong because two above mentioned queries give the same result. It looks like the first query orders records also by num behind curtains. Could you please suggest what is wrong with my assumptions and why these queries return same results?

Result of both above mentioned queries:

ID     NAME     NUM     LV

----------------------------------------

5      alan            1          5

2      alan            4          5

1      alan            5          5

7      fero            1           1

3      julia          10         10

6      maros      14         77

9      maros      55         77

4      maros      77         77

8      matej         8           8

This post has been answered by mathguy on Mar 10 2019
Jump to Answer
Comments
Post Details
Added on Mar 10 2019
6 comments
757 views