Sort

Add a Sort node to your Data Flow to sort tables by column data in ascending or descending order. If the given column contains text (string) values, sorting in ascending order sorts the column alphabetically and sorting in descending order sorts the column in reverse alphabetical order. If the given column contains numeric values they are, likewise, sorted from lowest to highest where ascending is selected and from highest to lowest where descending is selected.

A single node can sort your table by multiple columns in a nested sort (sometimes also known as "Hierarchical," "Multi-level," or "Sort by, then by" sorting). Nested sorting sorts first by one column, and then, where the values in that column are identical, by another; for example, sorting a list of people by last name, then by first name where the last names are the same, then by middle name or initial where the first names are the same, and so on.

Sorting tables

Before you add a Sort node, you should already have a Data Flow with a Source and at least one Table node on the canvas.

Adding a Sort node

The following describes how to add a simple Sort node, using the example of an Employees table being sorted by LastName and then, where the employees share a last name, by FirstName in ascending (alphabetical) order:

  1. Drag a Sort node onto the canvas from the Preparation Elements panel (purple arrow below).

  2. Connect the Table node to the Sort node.

    3. Note: When you drop the new connector, the name of the Sort node is updated to match the name of the table.

  3. Select the Sort node on the canvas (orange arrow below) and configure its properties in the right-hand Properties panel (blue highlight):
    1. Click the Plus icon (yellow arrow) to add details of a sort column.

      2. The Sort dialog opens.

    2. Specify the details of the sort:

      • Column Name: Select the column that you want to sort by. In our example, this is LastName as we are sorting the Employee table by last name.

      • Sort: Select the Sort order (Ascending or Descending). In our example, choose Ascending as we want to enforce alphabetical order on our names.

    3. Click Apply.

      4. The Sort dialog closes and its details are added to the properties table.

  4. Repeat the previous step for each additional column that you wish to sort on. In our example, this is FirstName as we want to sort any employees who share their last name by first name.

    5. Note: Each pair that you add to the table will be used in sequence in a nested sort. For more information, see Sequencing example (below).

  5. Recommended: Specify a name for the Sort node in the Resulting Table Name field (blue arrow).

    6. Tip: This value is shown on the Sort node in the diagram, so supply a name that describes exactly what the sort does.

  6. With the Sort node selected, click the Eye icon (green arrow) to run the preview.

A preview of the sorted Employees table is shown at the bottom of the canvas (yellow highlight):

Important: Where the final sorted list still includes unsorted members (members whose sort columns all contain identical values), those members are always in the database order. This is also how they are shown in the preview.

Sort Node properties

When you have the Sort node selected in the Data Flow, the Sort Node details are visible in the Properties panel:

This example shows that we have added three nested sort instructions, which are processed in sequence from the top item to the bottom. The example sorts the Employees table by LastName, then FirstName where the last names are identical, then MiddleName where the last and first names are identical. Each of these is an Ascending order sort, so each sorts alphabetically.

The options in this panel allow you to manage the nested sorting:

  • Handle: Orange arrow. Use the handles to the left of each of your sort instructions to drag the items into a different position, changing the sequence they are processed in. Note that dragging the FirstName instruction above LastName causes the table to be sorted by the FirstName column first and only by the LastName column where two Employees have the same first name.
  • Add: Green arrow. Open the Sort dialog and create an additional sort instruction. Each new row is added to the end by default.
  • Delete: Blue arrow. Delete the sort instruction on the given row.

Sequencing example

To further illustrate how the "sort by, then by" nested sort node works, let's step through an example where we apply each of these sort instructions to the node in sequence.

Imagine your Employees table includes the users shown in the first column:

 

LastName, Ascending

+ FirstName, Ascending

+ MiddleName, Ascending

  • Johnson, Andrew Barry
  • Johnson, Andrew Connor
  • Johnson, Andrea
  • Johnson, Barry Andrew
  • Johnson, Andrew Barry
  • Christiane, Julie Andrea
  • Christiane, Julie Andrea
  • Johnson, Andrew Barry
  • Johnson, Andrew Connor
  • Johnson, Andrea
  • Johnson, Barry Andrew
  • Johnson, Andrew Barry
  • Christiane, Julie Andrea
  • Johnson, Andrea
  • Johnson, Andrew Barry
  • Johnson, Andrew Connor
  • Johnson, Andrew Barry
  • Johnson, Barry Andrew
  • Christiane, Julie Andrea
  • Johnson, Andrea
  • Johnson, Andrew Barry
  • Johnson, Andrew Barry
  • Johnson, Andrew Connor
  • Johnson, Barry Andrew
  • If your Sort Node contains only the top sort instruction (LastName, Ascending), then the list is sorted into alphabetical order by LastName. This moves Julie Andrea Christiane to the top, since her last name is first alphabetically.
  • If you add the next sort instruction seen above (making the sort instructions LastName, Ascending and then FirstName, Ascending), then the Johnsons are moved into alphabetical order based on their first names. Andrea is placed first in the list of Johnsons, Barry is placed last, and the three Andrews sit between them.
  • If you add the next sort instruction seen above (adding MiddleName, Ascending), then the Andrew Johnsons are moved into alphabetical order based on their middle names. Note that this places the two Andrew Barry Johnsons above Andrew Connor Johnson.

Important: After all the nested sort instructions have been applied, the two Employees called Andrew Barry Johnson are still not sorted alphabetically; this is because they have identical names. They will always appear in the same order, but that will be the database order (appearance order) rather than an explicitly sorted order.

Multiple Nodes

To ensure that your sorting operations have the effect you intend, it is recommended that you only add one Sort node per table and build your nested sort using the Sort Node table for that one node. If you have a table that is connected to multiple Sort nodes in a sequence (perhaps from a version of Pyramid before nesting was introduced):

  • The connected Sort nodes are processed in reverse sort order. This means that, assuming the preceding nodes have only one Column Sort pair each, Pyramid will sort the Table first by LastName, then by FirstName, and then by MiddleName.
  • If your nodes have multiple Column Sort pairs each, the nodes will be processed in reverse order, but the nesting will be applied within each node. This is likely to be confusing and so is not recommended.

Tip: When you want to run the preview, be sure to select the last of the Sort nodes and then click the Eye icon (orange arrow above). This ensures that all parts of your nested sort are applied in the preview.

Related information

Common Properties

There are a number of fields that are present in the Properties panel when you have any of the preceding nodes selected on the canvas. These fields include Result Properties, Column Selection, Set Variable Values, and Metadata.

  • Click here for more details about the Common Properties