Statistic
Select by comparing the source field value using the given Condition to other values in the table.
Condition options:
- maximum - selects records with the maximum value,
- minimum - selects records with the minimum value,
- median - selects records with the median value,
- top - selects the specified number of records with the largest values,
- bottom - selects the specified number of records with the smallest values,
- top percent - selects records with values larger than the specified percentile, with an option to include or exclude the bounding percentage.
- bottom percent - selects records with values smaller than the specified percentile, with an option to include or exclude the bounding percentage.
Other options:
- Ignore case - Ignore upper or lower case when comparing values. Checked by default.
- Ignore whitespace at start and end - Ignore any whitespace characters (space, tab) at the beginning or end of a text value when comparing values. Checked by default.
For examples of how the above options can affect lexicographical sort order, see the Select - Text: Unique topic.
If we would like to select records that are logical NOT variations of the above, for example not maximum, select on the basis of one of the above and press Ctrl-I or Edit - Select Inverse to invert the selection. Selecting using maximum and then pressing Ctrl-I is the equivalent of "not maximum".
Launch the template by choosing a text field in the Select pane and then double-clicking the Statistic template. When the template launches we can specify options.
Statistic : maximum
Select records with the lexicographically maximum value in the table.
We start with a table with a single Country field of type nvarchar. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Country field and then we double-click the Statistic template to launch it.
In the Statistic template we choose maximum as the Condition option. We leave the Ignore case and Ignore whitespace at start and end boxes checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Country field, to sort the field from lowest value to highest value.
Records with an Country field value of Switzerland, the lexicographically maximum value that occurs in that field in the table, are selected.
Statistic : minimum
Select records with the lexicographically minimum value in the table.
We start with a table with a single Country field of type nvarchar. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Country field and then we double-click the Statistic template to launch it.
In the Statistic template we choose minimum as the Condition option. We leave the Ignore case and Ignore whitespace at start and end boxes checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Country field, to sort the field from lowest value to highest value.
Records with an Country field value of Austria, the lexicographically minimum value that occurs in that field in the table, are selected. In this table, there is only one record that has the minimum value.
Statistic : median
Select records with the lexicographically median value in the table.
We start with a table with a single Country field of type nvarchar. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Country field and then we double-click the Statistic template to launch it.
In the Statistic template we choose median as the Condition option. We leave the Ignore case and Ignore whitespace at start and end boxes checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Country field, to sort the field from lowest value to highest value.
Records with an Country field value of Hungary, the lexicographically median value that occurs in that field in the table, are selected.
Statistic : top
Select the specified number of records with the lexicographically largest values.
We start with a table with a single Country field of type nvarchar. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Country field and then we double-click the Statistic template to launch it.
In the Statistic template we choose top as the Condition option. For the Records number, we enter 5. We leave the Ignore case and Ignore whitespace at start and end boxes checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Country field, to sort the field from lowest value to highest value.
The 5 records with the lexicographically highest values in the table in their Country fields are selected. Two of those records have the same value, Switzerland. If five records had the highest value, Switzerland, those five would have been selected.
Statistic : bottom
Select the specified number of records with the lexicographically smallest values.
We start with a table with a single Country field of type nvarchar. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Country field and then we double-click the Statistic template to launch it.
In the Statistic template we choose bottom as the Condition option. For the Records number, we enter 5. We leave the Ignore case and Ignore whitespace at start and end boxes checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Country field, to sort the field from lowest value to highest value.
The 5 records with the lexicographically lowest values in the table in their Country fields are selected. Two of those records have the same value, Denmark, and another two of those records also have the same value, France. The fifth record has a unique values, Austria. If five records had the lowest value, Austria, those five would have been selected.
Statistic : top percent
Select records with values lexicographically larger than the specified percentile, with an option to include or exclude the bounding percentage.
We start with a table with a single Country field of type nvarchar. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Country field and then we double-click the Statistic template to launch it.
In the Statistic template we choose top percent as the Condition option. For the Percent number, we enter 25. We leave the Ignore case and Ignore whitespace at start and end boxes checked, the default.
We uncheck the Include bound box.
For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Country field, to sort the field from lowest value to highest value. That helps make clearer the distribution of values in the 14 records in the table.
In this table, there are 14 records, so 25 percent of those records would be 3.5 records if the values within the records were evenly distributed. However, the values are not evenly distributed lexicographically. Two of the records are the same value, Switzerland, the maximum value in the table.
With the Include bound box unchecked, the template selects the 3 records with top values. Records with the value of Norway or greater are in percentiles lower than the 25th percentile. Records with a value of Netherlands or less are not selected, because to get those we have to include the 25th percentile.
We can see how the selection would be different with the Include bound box checked.
We check the Include bound box. Press Select.
This time, the record containing the Netherlands values also is selected. Four records are selected because the values in records are not evenly distributed in the 14 record population of the table.
See additional examples of how variation in table population can affect selection by top and bottom percentiles in the Select - Numbers: Statistic topic.
Statistic : bottom percent
Select records with values lexicographically smaller than the specified percentile, with an option to include or exclude the bounding percentage.
We start with a table with a single Country field of type nvarchar. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Country field and then we double-click the Statistic template to launch it.
In the Statistic template we choose bottom percent as the Condition option. For the Percent number, we enter 25. We leave the Ignore case and Ignore whitespace at start and end boxes checked, the default.
We uncheck the Include bound box.
For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Country field, to sort the field from lowest value to highest value. That helps make clearer the distribution of values in the 14 records in the table.
Three records in the Country field are selected, the three records that fall into less than the 25th percentile of least values is that single record.
We can see how the selection would be different with the Include bound box checked.
We check the Include bound box. Press Select.
This time, two additional records containing the France values are also selected. Five records are selected because the values in records are not evenly distributed in the 14 record population of the table.
See additional examples of how variation in table population can affect selection by top and bottom percentiles in the Select - Numbers: Statistic topic.