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 columns they are, likewise, sorted from lowest to highest where ascending is selected and from highest to lowest where descending is selected.

You can use each Sort node to sort your table by a single column. You can also add multiple Sort nodes in reverse order to use nested sorting (sometimes also known as "hierarchical" or "multi-level" sorting) to sort first by one column, and then, where the values are identical, further sort by another column; for example, sorting a list of people by last name, then by first name where the last names are the same, then by middle initial where the first names are the same, and so on.

Note: This topic describes Pyramid 2023.16 functionality. If you are using an earlier version of Pyramid, you should be aware that adding multiple connected Sort nodes may have an unpredictable effect on the sorted table. You should also be aware that, prior to Pyramid 2023.16, identical members (for example, users who share a last name) may not be ordered predictably in sorted columns. You can see which version you are using in the System Info panel.

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.

Sort by a single column

The following describes how to add a simple Sort node, using the example of an Employees table being sorted by the LastName of those employees in ascending (alphabetical) order:

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

  2. Connect the Employees Table node to the Sort node.

  3. Select the Sort node on the canvas (orange arrow below) and configure its properties in the right-hand Properties panel (blue highlight):

    • Resulting Table Name: Specify a name for the node. Tip: This value is shown on the diagram, so supply a name that describes what the sort does.
    • 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.

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

You can see the sorted column in the preview at the bottom of the canvas (yellow highlight):

Important: Where the sorted list includes identical members (for example, multiple users with the last name Johnson), those members are always ordered as they appear in the preview.

Sort by X then Y (nested sort)

If you cannot guarantee that your columns have unique contents, you will typically want to define what should happen where the values are the same; for example, where more than one user has the last name "Johnson." In this case, you can create a "nested sort," which sorts identical values by another column; for example, where there are multiple instances of the last name "Johnson" it further sorts the Johnsons by their first names, and, if there are multiple Johnsons with the same first name and last name, it sorts those Johnsons by middle name.

This functionality is enabled by adding multiple Sort nodes onto your canvas that describe each of the sort functions you want to apply, and then connecting those nodes in reverse sort order:

Things to notice about this example:

  1. Three Sort nodes are added to the canvas, describing the three sorts that you want to nest:
    • Sort node 1: Column Name: LastName, Sort: Ascending.
    • Sort node 2: Column Name: FirstName, Sort: Ascending.
    • Sort node 3: Column Name: MiddleName, Sort: Ascending.
  2. The Sort nodes are connected in reverse sort order. Because you want to sort first by LastName, then by FirstName, and then by MiddleName, you need to connect:
    • Table node to the Sort node that filters by MiddleName (Sort node 3).
    • Then MiddleName to FirstName (Sort node 2).
    • Lastly, FirstName to LastName (Sort node 1).

      • Important: This is the reverse of the sort order that you are defining.

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). This ensures that all parts of your nested sort are applied in the preview.

Tip: If your Sort filters all include non-unique values (as is possible with our example), you might want to consider adding an initial Sort node where all column values are unique, such as Social Security number.