Small – PB Docs 126

Small DataWindow expression function


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.





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.


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.


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

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.

Return Values

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


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
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:

  • For the Graph or OLE presentation style, Rows is
    always All.

  • For Graph controls, Rows can be All, Page, or Group.

  • For OLE controls, Rows can be All, Current Row,
    Page, or Group. The available choices depend on the layer the control


Min 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.


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:

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

See Also

Document get from Powerbuilder help
Thank you for watching.
Was this article helpful?
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x