First, Last Functions

Return a field value from the first or last record in the result set returned by a query.

Syntax

First( expr )

Last( expr )

The expr placeholder represents a string expression identifying the field that contains the data you want to use or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate functions).

Remarks

These functions return the value of a specified field in the first or last record, respectively, of the result set returned by a query. If the query does not include an ORDER BY clause, the values returned by these functions will be arbitrary because records are usually returned in no particular order.

Examples

Expression

Results

SELECT First(DateofSale) AS Expr1 FROM ProductSales;

Return the first value from the field "DateofSale" in the table ProductSales.

SELECT Last(UnitPrice) as LastPrice, First(DateofSale) AS FirstDate FROM ProductSales;

Return the Last value from the field "UnitPrice" and First value from the field "DateofSale", displays them in column LastPrice and FirstDate respectively.

See Also

Calculating Fields in SQL Functions

Expand your Office skills
Explore training
Get new features first
Join Office Insiders