Ordering Aggregate Functions
Ordering aggregate functions order the aggregated rows.
FIRST_VALUE WITHIN GROUP and LAST_VALUE WITHIN GROUP
FIRST_VALUE (scalar_value_expression) [null_specification] WITHIN GROUP (order_by_clause)...[group_by_clause]
LAST_VALUE (scalar_value_expression) [null_specification] WITHIN GROUP (order_by_clause)...[group_by_clause]
Produces one row per group, or if there is no group_by_clause, a single row
where:
scalar_value_expr
Specifies any scalar expression that can appear in the select list of a query. Can contain constants, row values, operators, scalar functions, and scalar subqueries.
null specification
Indicates whether to include null values in the selection of a first_value or last_value. Valid values are:
RESPECT NULLS
(Default) Uses the expression value in the first or last row.
IGNORE NULLS
Uses the first or last non-null expression value.
The following query shows the employee number (empno) of the highest paid employee in each department:
SELECT deptno, FIRST_VALUE(empno)
WITHIN GROUP (ORDER BY sal DESC) AS "FIRST VALUE"
FROM emp GROUP BY deptno;
DEPTNO FIRST VALUE
---------- -----------
100 839
200 788
300 900