Small

Description

Finds a small value at a specified ranking in a column (for example, third-smallest, fifth-smallest) and returns the value of another column or expression based on the result.

Syntax

Small ( returnexp, column, nbottom { FOR range { DISTINCT { expres1 
	{,  expres2 {, ... } } } } } )

Argument

Description

returnexp

The value you want returned when the small value is found. Returnexp includes a reference to a column, but not necessarily the column that is being evaluated for the small value, so that a value is returned from the same row that contains the small value.

column

The column that contains the small value you are searching for. Column can be a column name or a column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. The datatype of column must be numeric.

nbottom

The relationship of the small value to the column’s smallest value. For example, when nbottom is 2, Small finds the second-smallest value.

FOR range (optional)

The data that will be included when finding the small value. For most presentation styles, values for range are:

  • ALL – (Default) The small value of all rows in column.

  • GROUP n – The small value of rows in column in the specified group. Specify the keyword GROUP followed by the group number: for example, GROUP 1.

  • PAGE – The small value of the rows in column on a page.

For Crosstabs, specify CROSSTAB for range:

  • CROSSTAB – (Crosstabs only) The small value of all rows in column in the crosstab.

For Graph and OLE objects, specify one of the following:

  • GRAPH – (Graphs only) The small value in column in the range specified for the Rows option.

  • OBJECT – (OLE objects only) The small value in column in the range specified for the Rows option.

DISTINCT (optional)

Causes Small to consider only the distinct values in column when determining the small value. For a value of column, the first row found with the value is used and other rows that have the same value are ignored.

expresn (optional)

One or more expressions that you want to evaluate to determine distinct rows. Expresn can be the name of a column, a function, or an expression.

Returns

The datatype of returnexp. Returns the nbottom-smallest value if it succeeds and -1 if an error occurs.

Usage

If you specify range, Small returns the value in returnexp when the value in column is the nbottom-smallest value in range. If you specify DISTINCT, Small returns returnexp when the value in column is the nbottom-smallest value of the distinct values in column, or if you specify expresn, the nbottom-smallest for each distinct value of expresn.

For graphs and OLE objects, you do not select the range when you call the function. The range has already been determined by the Rows setting on the Data property page (the Range property), and the aggregation function uses that range.

Settings for Rows include the following:

NoteMin might be faster If you do not need a return value from another column and you want to find the smallest value (nbottom = 1), use Min; it is faster.

Not in validation rules or filter expressions You cannot use this or other aggregate functions in validation rules or filter expressions.

Using an aggregate function cancels the effect of setting Retrieve Rows As Needed in the painter. To do the aggregation, a DataWindow object always retrieves all rows.

Examples

Example 1

These expressions return the names of the salespersons with the three smallest sales (sum_sales is the sum of the sales for each salesperson) in group 2, which might be the salesregion group. Note that sum_sales contains the values being compared, but Small returns a value in the name column:

Small(name, sum_sales, 1 for group 2)
Small(name, sum_sales, 2 for group 2)
Small(name, sum_sales, 3 for group 2)

Example 2

This example reports the salesperson with the third-smallest sales, considering only the first entry for each salesperson:

Small(name, sum_sales, 3 for all DISTINCT sum_sales)

See also