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