Sort
Overview
Using the Sort tool, you can reorder an input table based on a key or combination of keys. Data Management's Sort tool is quite flexible, supporting:
Ascending or descending order for each key.
As many sort keys as you want.
Strongly-typed sort keys. For example numbers, dates, and times will sort properly.
Sorting in Data Management is a very fast process—typically many times faster than using an RDBMS to produce a sorted output. You will, however, need temporary disk space equal to the size of the table being sorted.
Sort is a general-purpose tool that is useful in many situations. For example:
In merge-purge and householding processes, use this tool to order the data for comparison. Choose your ordering so as to place likely duplicate records near each other. (See Merge/purging a set of mailing lists.)
When preparing data for bulk-loading of a database, the database may require the records to be sorted by the primary key. Use the Sort tool to order the data so as to fulfill the bulk-loader requirements.
When using the First N or Last N tools to find the best or worse records, use the Sort tool to order the records by the "goodness" field. For example, to find your top ten customers, first Sort your customers by total purchase amount, and then use First N to find the first ten.
Sort tool configuration parameters
The Sort tool has one set of configuration parameters in addition to the standard execution options.
Parameter | Description |
|---|---|
Field | Name of the input field. |
Order | Sort order, either Ascending or Descending. This is optional and defaults to Ascending. |
Configure the Sort tool
Select the Sort tool, and then go to the Configuration tab on the Properties pane.
Select in the Field column and select the field to sort on. The first row of the grid defines the primary sort key, the second row defines the secondary sort key, and so on.
Text fields will be sorted lexically, but other field types will be sorted in a manner appropriate to the data type. For example, a Date field will sort "Jan 1, 1999" before "Mar 1, 1999."
Select in the Order box and choose either Ascending or Descending sort order.
Repeat steps 2 and 3 to add additional sort keys.
Optionally, go to the Execution tab, and then set Web service options.
When records are sorted in ascending order, any Null values appearing in the key fields sort after all valid values. Any error values will sort after the Null values. However, Nulls and Errors sort at the beginning of textual data. When sorting in descending order, the opposite is true.