Database Reference
In-Depth Information
--------------+-----------------+---------------------+-----------
40 | OPERATIONS | BOSTON | 40
30 | SALES | CHICAGO | 40
30 | SALES_OUTER | CHICAGO | 40
30 | SALES_INSIDE | CHICAGO | 40
(4 rows)
The last_value() function
The
last_value()
function is used to get the value evaluated at the last row of the
window frame. The
last_value()
function takes the column name as the input
argument. Let's understand this with the following example:
warehouse_db=# SELECT department_no, department_name,
department_location, last_value(department_no) OVER
(ORDER BY department_name) FROM
warehouse_departments;
department_no | department_name | department_location | last_value
---------------+-----------------+---------------------+----------
10 | ACCOUNTING | NEW YORK | 10
40 | OPERATIONS | BOSTON | 40
20 | RESEARCH | DALLAS | 20
20 | RESEARCH_OPEN | PARIS | 20
30 | SALES | CHICAGO | 30
30 | SALES_INSIDE | CHICAGO | 30
30 | SALES_OUTER | CHICAGO | 30
(7 rows)
The last record that we get on the basis of the
ORDER BY
clause in
department_name
is
SALES_OUTER
with the value of
department_no
equal to
30
. Hence, the value of
last_value
is
30
.
The nth_value() function
The
nth_value()
function is used to get a value evaluated at the row that is the nth
row of the window frame. The
nth_value()
function takes the column name and
nth number as the input argument. It returns the null value if the nth value is not
present in the table. Let's understand this with the following example:
warehouse_db=# SELECT department_no, department_name,
department_location,
nth_value(department_location,2) OVER (PARTITION BY
department_no ORDER BY department_name) FROM
warehouse_departments;
