Term: FIRST

Definition:
The Oracle PL/SQL FIRST function can be used for both aggregate and analytic action on the group of logically sorted rows. It operates on values of these rows that are eligible to be ranked "first" or "last". The rows can be grouped using any of the analytic functions like MIN, MAX, SUM, AVG, COUNT, VARIANCE or STDDEV.

Example Syntax:

[AGGREGATE FUNCTION] KEEP (DENSE_RANK FIRST ORDER BY [COLUMN | EXPRESSION] [ASC | DESC] )

OVER (PARTITION BY [COLUMN] )

In the syntax, the KEEP keyword is used as an indication that only FIRST values are returned. Note that DENSE_RANK is always used to rank the grouped rows and extract the minimum or maximum value.

Notes:

FIRST and LAST are the only functions that deviate from the general syntax of analytic functions.
FIRST and LAST do not have the ORDER_BY inside the OVER clause.
FIRST and LAST do not support any <window> clause.

Example Usage:

The SQL query below displays the salary of each employee in a department. At the same time it also shows the lowest salary in their department.

SELECT DEPTNO, ENAME ,SAL

       MIN(sal) KEEP (DENSE_RANK FIRST ORDER_BY sal) OVER (PARTITION BY deptno) "Lowest"

FROM   EMP

ORDER BY deptno, sal;

Related Links:

Related Code Snippets:

FIRST - Returns the row ranked first using DENSE_RANK.
FIRST_VALUE - Returns the first value in an ordered set of values. If the first value in the set i...
Date: Find first day of the month - Returns the first day of a given month.
INSERT FIRST WHEN - The WHEN clause is evaluated in the order in which it appears in the statement...
RegExp: Append first name first letter to last name - Example of using a regular expression to get...