Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Functions » Alphabetical list of functions

EXPRTYPE function [Miscellaneous] Next Page

FIRST_VALUE function [Aggregate]


Returns values from the first row of a window.

Syntax

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

window-spec : see the Remarks section below

Parameters

expression    The expression to evaluate. For example, a column name.

Remarks

The FIRST_VALUE function allows you to select the first value (according to some ordering) in a table, without having to use a self-join. This is valuable when you want to use the first value as the baseline in calculations.

The FIRST_VALUE function takes the first record from the window. Then, the expression is computed against the first record and results are returned.

If IGNORE NULLS is specified, the first non-NULL value of expression is returned. If IGNORE NULLs is not specified, the first value is returned whether or not it is NULL.

The FIRST_VALUE function is different from most other aggregate functions in that it can only be used with a window specification.

Elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. See the window-spec definition provided in WINDOW clause.

For more information about using window functions in SELECT statements, including working examples, see Window functions.

See also
Standards and compatibility
Example

The following example returns the relationship, 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 StartDate DESC ) ) 
           AS percentage
    FROM Employees;

In the result set below, since employee 1658 is the first row for department 500, you know that they are the most recent hire in that department; therefore, their percentage is set to 100%. Then, 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.

If another employee in the same department makes the same salary as the most recent hire, they will have a percentage of 100 as well.

DepartmentIDEmployeeIDpercentage
5001658100
5001615110.4284624
5001570138.8427097
5001013109.5851905
500921167.4497049
500868113.2393688
500750137.7344095
500703222.8679276
500191119.6642975
4001751100
400174099.705647
4001684130.969936
400164383.9734797
4001607175.1828989
4001576197.0164609
.........