FIRST_VALUE function [Aggregate]

Function

Returns the first value from a set of values.

Syntax

FIRST_VALUE (expression [IGNORE NULLS | RESPECT NULLS])
OVER (window-spec)

Parameters

expression The expression on which to determine the first value in an ordered set.

Usage

FIRST_VALUE returns the first value in a set of values, which is usually an ordered set. If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. If you specify IGNORE NULLS, then FIRST_VALUE returns the first non-null value in the set, or NULL if all values are null.

The data type of the returned value is the same as that of the input value.

You cannot use FIRST_VALUE or any other analytic function for expression. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expression.

If the window-spec does not contain an ORDER BY, then the result is arbitrary. If there is no window-spec, the answer is arbitrary.

You can specify elements of window-spec either in the function syntax (inline), or with a WINDOW clause in the SELECT statement. For information on how to specify the window, see “Analytical functions”.

NoteDISTINCT is not supported.

Example

The following example returns the relationship, expressed as a percentage, between each employee’s salary and that of the most recently hired employee in the same department:

SELECT DepartmentID, EmployeeID,
100 * Salary / ( FIRST_VALUE( Salary ) OVER (
PARTITION BY DepartmentID  ORDER BY Year(StartDate) DESC ) )
AS percentage
FROM Employees order by DepartmentID DESC;

The returned result set is:

Table 4-22: FIRST_VALUE result set

DepartmentID

EmployeeID

Percentage

500

1658

100.000000000000000000000

500

1570

138.842709713689113761394

500

1615

110.428462434244870095972

500

1013

109.585190539292454724330

500

750

137.734409508894510701521

500

921

167.449704854836766654619

500

868

113.239368750752921334778

500

703

222.867927558928643135365

500

191

119.664297474199895594908

400

1684

100.000000000000000000000

400

1740

76.128652163477274215016

400

1751

76.353400685155687446813

400

1607

133.758100765890593292456

400

1507

77.996465120338650199655

400

1576

150.428767810774836893669

In this example, employee 1658 is the first row for department 500, indicating that employee 1658 is the most recent hire in that department, and therefore receives a percentage of 100%. Percentages for the remaining employees in department 500 are calculated relative to that of employee 1658. For example, employee 1570 earns approximately 139% of what employee 1658 earns.

Standards and compatibility