3 6 Sort Your Data

LANSA Client

3.6 Sort Your Data

To make your query's results easier to read, you may wish to sort the data into a more useable sequence.

You can specify your sorting requirements either from the Query window, before the query is run, or from the Results window after the query has been run. There are advantages to sorting your data before you run the query. These are explained in 3.11 Performance Considerations.

To specify your sorting requirements, select the Sort Options command from the Options menu or select this icon from the toolbar.

When you select this command, the Sort Options dialog box appears.

 

The Sort Options dialog box contains:

Field

A list of the fields you can sort on. You can include more than one field in a sort.

Sort Type

The sort type indicates that you wish to select the corresponding field for sorting. Choose the sequence you want by clicking on the arrow to the right of this box and select either:

  • Ascending, so that the results are returned in the sequence lowest to highest.
  • Descending, for results in highest to lowest sequence.
  • None will remove a field from the sort.

Display field in results

A tick in this box indicates that the field will be displayed in the results.

For example, your query may include a "Name Sequence" field which is used to sequence your customer names but you only wish to see the customer's name in your results. In this case, you would specify ascending or descending Sort Type for the Name Sequence and remove the tick from the corresponding Display field in results box.

As another way of using this option, imagine that you have defined a query which retrieved Field1, Field2 and Field3. Having seen your results, you decide that you only want to chart the results from Field1 and Field3. Rather than redefine and rerun the query to retrieve only Field1 and Field3, turn off the display of Field2 using this Display field in results option. Then you can simply chart the results from Field1 and Field3.

Note: You cannot turn off the display of a field that has Group or Summary options added to it.

Re-sequencing your Selected Fields

You can re-arrange the order of the fields in a sort by highlighting a field and selecting either the Up or Down buttons located at the right side of the dialog box.

For example, your query may consist of FieldA, FieldB and FieldC and you want to sort your data using FieldC  then FieldB. In this case, you would attach the required Sort Type to Fields C & B, then re-arrange the fields by highlighting FieldC and using the Up button to move it above FieldB in the Field list.

Saving your Sort Options

Once you are satisfied with your sort options, select the OK button. If you want to ignore any changes you have made, select the Cancel button.

Select the Reset button to remove all the sorting criteria.

Note on Sorting Data

If you have specified a sort for your query, LANSA Client will determine whether it can sort your data at the host rather than at your workstation.

Depending on where it is sorted, the result may be different.
For example if, your data is sorted into ascending sequence on the IBM i, numeric data will follow alphabetic data. If the same data is sorted into ascending sequence on a workstation, the numeric data will come first. The reverse will apply with descending sequence.

Sometimes you will want to control where your data is sorted. As a general rule, LANSA Client will sort the data at the host if:

  • your query is a join query.
  • you have attached selection conditions to non-key fields and there are no virtual fields defined in your query.
  • your query contains a LIKE selection condition.

The result of your query may vary when the Maximum Record number is used to limit the number of records retrieved. To avoid any variations, you can either run your query using a logical file which is already sorted into the sequence you want, (the first 'n' records are always retrieved) or define your query so that the sorting is always done at the host.

Top-10 Style Query

By using a combination of Sort options and Maximum records, you can create a Top-10 style query. You could use this style of query to obtain, for example, the ten salesmen with the highest sales or your 6 best selling products. To do this, you need to ensure that the data is sorted in descending sequence on the IBM i. To ensure that the data is sorted on the host, either create a join query or use the Like operator with an All Values prompt, and use the Output Options command to set the Maximum records to 10, or the number you require.

Further Information

Ý 3. Create and Run Queries