FIRST

Syntax

first::=

Purpose

FIRST andLAST are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as theFIRST orLAST with respect to a given sorting specification. If only one row ranks asFIRST orLAST, then the aggregate operates on the set with only one element.

If you omit theOVER clause, then theFIRST andLAST functions are treated as aggregate functions. You can use these functions as analytic functions by specifying theOVER clause. Thequery_partition_clause is the only part of theOVER clause valid with these functions. If you include theOVER clause but omit thequery_partition_clause, then the function is treated as an analytic function, but the window defined for analysis is the entire table.

These functions take as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, theFIRST andLAST functions eliminate the need for self-joins or views and enable better performance.

• Theaggregate_function argument is any one of theMIN,MAX,SUM,AVG,COUNT,VARIANCE, orSTDDEV functions. It operates on values from the rows that rank eitherFIRST orLAST. If only one row ranks asFIRST orLAST, then the aggregate operates on a singleton (nonaggregate) set.

• TheKEEP keyword is for semantic clarity. It qualifiesaggregate_function, indicating that only theFIRST orLAST values ofaggregate_function will be returned.

• DENSE_RANKFIRST orDENSE_RANKLAST indicates that Oracle Database will aggregate over only those rows with the minimum (FIRST) or the maximum (LAST) dense rank (also called olympic rank).

Aggregate Example

The following example returns, within each department of the sample tablehr.employees, the minimum salary among the employees who make the lowest commission and the maximum salary among the employees who make the highest commission:

SELECT department_id,       MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"  FROM employees  GROUP BY department_id  ORDER BY department_id;DEPARTMENT_ID      Worst       Best------------- ---------- ----------           10       4400       4400           20       6000      13000           30       2500      11000           40       6500       6500           50       2100       8200           60       4200       9000           70      10000      10000           80       6100      14000           90      17000      24000          100       6900      12008          110       8300      12008                    7000       7000

Analytic Example

The next example makes the same calculation as the previous example but returns the result for each employee within the department:

SELECT last_name, department_id, salary,       MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)         OVER (PARTITION BY department_id) "Worst",       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)         OVER (PARTITION BY department_id) "Best"   FROM employees   ORDER BY department_id, salary, last_name;LAST_NAME           DEPARTMENT_ID     SALARY      Worst       Best------------------- ------------- ---------- ---------- ----------Whalen                         10       4400       4400       4400Fay                            20       6000       6000      13000Hartstein                      20      13000       6000      13000. . .Gietz                         110       8300       8300      12008Higgins                       110      12008       8300      12008Grant                                   7000       7000       7000