Unique
Selects records based on how many times their values occur in a table using the given Condition:
Condition options:
- duplicate - for each duplicate value, selects all such records except one,
- non-unique - selects records with values that occur more than once,
- unique - selects records with values that occur only once.
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.
Launch the template by choosing a text field in the Select pane and then double-clicking the Unique template. When the template launches we can specify options.
Unique : duplicate
For each duplicate value, select all records except one.
We start with a table with a single Country field of type nvarchar. Some of the values are the same except for the use of upper and lower case, and some of the values are the same except for leading space characters. 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 Unique template to launch it.
In the Unique template we choose duplicate 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. That makes clear how the template selects all duplicates but one for records that have duplicate values in the Country field.
Note that the description of what the duplicate option does is that it selects all duplicates except one record. It is not a matter of selecting all duplicates except the "first" record or except the "last" record. It simply picks at random one of the duplicate value records to not select out of records with duplicate values.
The first record, for France preceded by multiple space characters, has not been selected because it happened to be the one record of duplicate value records that was not selected.
Unique : non-unique
Select records with values that occur more than once, that is, selecting all records with duplicate values in the target field.
We start with a table with a single Country field of type nvarchar. Some of the values are the same except for the use of upper and lower case, and some of the values are the same except for leading space characters. 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 Unique template to launch it.
In the Unique template we choose non-unique 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. That makes clear how the template selects all records with duplicate values in the Country field.
We can see what the check boxes do by unchecking them.
For example, we can uncheck the Ignore case box and then press Select.
The result is that the record for FRANCE in all capital letters is not selected as a duplicate, and the records for netherlands and Netherlands are not selected as duplicates because of the difference in case in their first letters.
We check Ignore case to restore the default, and then we uncheck the Ignore whitespace at start and end box, to see what it does.
Press Select.
As expected, records that have no leading or trailing spaces and are identical except for case are selected as duplicates. Records which have leading or trailing whitespace, but which are otherwise identical except for case are not selected as duplicates.
Unique : unique
Select records with values that occur only once.
We start with a table with a single Country field of type nvarchar. Some of the values are the same except for the use of upper and lower case, and some of the values are the same except for leading space characters. 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 Unique template to launch it.
In the Unique template we choose unique 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. That makes clear how the template selects all records that have unique values, that is, that do not have duplicate values, in the Country field.
If case and leading and trailing whitespace are significant for us, we can turn off those boxes.
We uncheck the Ignore case and Ignore whitespace at start and end boxes and then we press Select.
The result is that records with values that differ in case or in leading or trailing whitespace are also selected as unique.