7 Replies Latest reply on Apr 23, 2013 7:20 AM by 1004795

# FIRST_VALUE() and LAST_VALUE Analytic Functions

Hi All,

May be this is a basic question. But I am having a hard time understanding the difference between FIRST_VALUE() and LAST_VALUE() Analytical Functions.

As far as what I have read FIRST_VALUE function picks up the first record after the partition and order by and returns it after any computation. And the LAST_VALUE does the opposite. But the result of the second query what I expected did not show up (last value of the partition). It would be of some help if anyone could throw some light on it.
``````select empno
, ename
, sal
, first_value(ename) over(order by sal desc)
from emp;

empno  ename      sal       first_value
------ ---------- --------- -----------
7839   KING       5000.00   KING
7902   FORD       3000.00   KING
7788   SCOTT      3000.00   KING
7566   JONES      2975.00   KING
7698   BLAKE      2850.00   KING
7782   CLARK      2450.00   KING
7499   ALLEN      1600.00   KING
7844   TURNER     1500.00   KING
7934   MILLER     1300.00   KING
7654   MARTIN     1250.00   KING
7521   WARD       1250.00   KING
7900   JAMES      950.00    KING
7369   SMITH      800.00    KING

14 Row(s) affected``````
``````select empno
, ename
, sal
, last_value(ename) over(order by sal desc)
from emp;

empno  ename      sal       last_value
------ ---------- --------- ----------
7839   KING       5000.00   KING
7902   FORD       3000.00   SCOTT
7788   SCOTT      3000.00   SCOTT
7566   JONES      2975.00   JONES
7698   BLAKE      2850.00   BLAKE
7782   CLARK      2450.00   CLARK
7499   ALLEN      1600.00   ALLEN
7844   TURNER     1500.00   TURNER
7934   MILLER     1300.00   MILLER
7521   WARD       1250.00   MARTIN
7654   MARTIN     1250.00   MARTIN
7900   JAMES      950.00    JAMES
7369   SMITH      800.00    SMITH

14 Row(s) affected``````
Thanks
Venkat
• ###### 1. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
Hi, Venkat,

When you use an analytic function with an ORDER BY clause, the results will be based on a window, which is a subset of the partition.
If you don't specify a window (by using the RANGE or ROWS keywords) the window will include everything in order by the ORDER BY clause up to and including the current row, including ties.

For example, in your second query:
``````select empno
, ename
, sal
, last_value(ename) over(order by sal desc)
from emp;

empno  ename      sal       last_value
------ ---------- --------- ----------
7839   KING       5000.00   KING
7902   FORD       3000.00   SCOTT
7788   SCOTT      3000.00   SCOTT
7566   JONES      2975.00   JONES
7698   BLAKE      2850.00   BLAKE
7782   CLARK      2450.00   CLARK
7499   ALLEN      1600.00   ALLEN
7844   TURNER     1500.00   TURNER
7934   MILLER     1300.00   MILLER
7521   WARD       1250.00   MARTIN
7654   MARTIN     1250.00   MARTIN
7900   JAMES      950.00    JAMES
7369   SMITH      800.00    SMITH    ``````
The analytic function
``last_value(ename) over(order by sal desc)``
will return the last ename, not of the entire table, but of the window starting with the highest sal (since you're saying "ORDER BY sal DESC"), and including the current row, and any other rows that happen to have the same sal.

So consider the 1st row, with ename='KING'. It has the higest sal, so there is only one row in the window, and 'KING' IS THE LAST VALUE IN THAT WINDOW.

Now consider the 2nd row, where ename='FORD' and sal=3000. The window will now include everybody with a sal of 3000 and higher, whihc means the 3 rows 'KING', 'FORD' and 'SCOTT'. The last of these (in descending order by sal) is 'SCOTT'. (Actually, there's a tie; you could equally well say that 'JONES' is last, since there's a tie between the two rows where sal=3000. When that happens, one of the rows will arbitrarily be designated the "last" row. don't expect it to always be the same row.)

Becuase of this, LAST_VALUE is alwmost always used with an explicit windowing clause, starting with RANGE or ROWS.

If you want a query like your first query, but it includes the name of the lowest paid employee (that is, it always says 'SMITH' in the last column instead of 'KING'), then use FIRST_VALUE, but reverse the sorting order:
``first_value(ename) over(order by sal ASC)``
• ###### 2. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
You may have to specify the clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Results are unchanged even if you use this clause for first_value function. I'm assuming the analytical function takes into account only the current row and the preceding row.
``````hr@orcl> select employee_id, first_name, last_name, salary, last_value(first_name) over
2  (order by salary desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
3  from employees;
100 Steven               King                      24000 TJ
101 Neena                Kochhar                   17000 TJ
102 Lex                  De Haan                   17000 TJ
145 John                 Russell                   14000 TJ
146 Karen                Partners                  13500 TJ
201 Michael              Hartstein                 13000 TJ
205 Shelley              Higgins                   12000 TJ
108 Nancy                Greenberg                 12000 TJ
147 Alberto              Errazuriz                 12000 TJ
168 Lisa                 Ozer                      11500 TJ
148 Gerald               Cambrault                 11000 TJ
174 Ellen                Abel                      11000 TJ
114 Den                  Raphaely                  11000 TJ
162 Clara                Vishney                   10500 TJ
149 Eleni                Zlotkey                   10500 TJ
169 Harrison             Bloom                     10000 TJ
..................
119 Karen                Colmenares                 2500 TJ
182 Martha               Sullivan                   2500 TJ
131 James                Marlow                     2500 TJ
144 Peter                Vargas                     2500 TJ
191 Randall              Perkins                    2500 TJ
127 James                Landry                     2400 TJ
135 Ki                   Gee                        2400 TJ
128 Steven               Markle                     2200 TJ
136 Hazel                Philtanker                 2200 TJ
132 TJ                   Olson                      2100 TJ``````
• ###### 3. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
Sundar M wrote:
I'm assuming the analytical function takes into account only the current row and the preceding row.
Analytical functions are calculated based on windows which defaults to rows between unbounded preceding and current row when not specified otherwise.

Best regards

Maxim
• ###### 4. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
Thanks a lot guys. That was very elaborate.
• ###### 5. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
1* select ename,deptno,sal,first_value(sal) over(),last_value(sal) over() from emp
SQL> /

ENAME DEPTNO SAL FIRST_VALUE LAST_VALUE
---------- ---------- ---------- ---------------------- ---------------------
KING 10 5000 5000 1300

BLAKE 30 2850 5000 1300

CLARK 10 2450 5000 1300

JONES 20 2975 5000 1300

MARTIN 30 1250 5000 1300

ALLEN 30 1600 5000 1300

TURNER 30 1500 5000 1300

JAMES 30 950 5000 1300

WARD 30 1250 5000 1300

FORD 20 3000 5000 1300

SMITH 20 800 5000 1300

SCOTT 20 3000 5000 1300

MILLER 10 1300 5000 1300

14 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select ename,deptno,sal,
2 first_value(sal)
3 over(partition by deptno) firstvalue,
4 last_value(sal)
5* over(partition by deptno) lastvalue from emp
SQL> /

ENAME DEPTNO SAL FIRSTVALUE LASTVALUE
---------- ---------- ---------- ---------- ----------

KING 10 5000 5000 1300

CLARK 10 2450 5000 1300

MILLER 10 1300 5000 1300

SCOTT 20 3000 1100 2975

SMITH 20 800 1100 2975

FORD 20 3000 1100 2975

JONES 20 2975 1100 2975

WARD 30 1250 1250 1500

JAMES 30 950 1250 1500

ALLEN 30 1600 1250 1500

MARTIN 30 1250 1250 1500

BLAKE 30 2850 1250 1500

TURNER 30 1500 1250 1500

14 rows selected.

Edited by: 1001792 on Apr 23, 2013 12:05 AM
• ###### 6. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
Welcome to the forum..

• ###### 7. Re: FIRST_VALUE() and LAST_VALUE Analytic Functions
1 select ename,
2 deptno,
3 sal,
4 first_value(sal) over() firstvalue,
5* last_value(sal) over() lastvalue from emp

1 select ename,deptno,sal,
2 first_value(sal)
3 over(partition by deptno) firstvalue,
4 last_value(sal)
5* over(partition by deptno) lastvalue from emp