FIRST()

Syntax

FIRST( column [, offset] ) GETTIMESTAMP(FIRST( name))

Parameters
column	The name of a column in a window.
offset	Which row to use, as offset from the first row in the window based on the window's sort order. If omitted or 0, uses the first row.
name	The name of a stream or window.

Data Types
Return	column	offset
Integer	Integer	Integer
Long	Long	Integer
Float	Float	Integer
Interval	Interval	Integer
Timestamp	Timestamp	Integer
String	String	Integer
Boolean	Boolean	Integer
BLOB	BLOB	Integer
XML	XML	Integer

Return	name
Timestamp	String

Usage

An offset of 0 for a window that is not sorted by largest or smallest value indicates the oldest row in the window (the first to arrive), while an offset of 1 indicates the next oldest row, and so on.

The offset for a window defined with a LARGEST or SMALLEST clause is relative to the sorting order as specified by the LARGEST or SMALLEST clause. For example, if you define a window to keep the largest values as sorted by its Price column, then FIRST(Volume) returns the value of the Volume column from the row in the window with the lowest value in its Price column. FIRST(Volume, 1) returns the value of the Volume column from the row containing the second-lowest value in the Price column, and so on.

The offset for an unnamed window partitioned by a GROUP BY clause, or a window partitioned by one or more PER clauses, is based on the partition to which the incoming row belongs, not on the entire window.

For example, this following scenario assumes you have a window grouped by its Symbol column and your query includes FIRST(Volume). When a row arrives in the window, FIRST(Volume) returns the value of the Volume column from the oldest row with a value in its Symbol column that matches the value in the new row.

When used with a join, Sybase CEP Engine always evaluates FIRST() in the context of the row or rows being processed. In other words, the FIRST function applies to the partition identified by the row or rows being processed. If the row is Null-extended as the result of an outer join, the partition is the Null partition.

A special case of the FIRST function is embedded in the GETTIMESTAMP function and returns the timestamp of the first row in the specified window.

FIRST() can be used in a WHERE selection condition and in a SELECT clause select list.

Example

The following example returns the price of the third-oldest trade record in the window:

INSERT INTO OutStream
SELECT FIRST(Trades.Price, 2)
FROM Trades;