Specifying Sort Order

Often you will want to make data easier to understand by presenting it in a particular order. You can sort the result at the time the query is executed by creating an ORDER BY clause for the SELECT statement, or you can sort columns after the result is returned.

The advantages of sorting the query results using an ORDER BY clause are:

• You can sort on columns that you do not retrieve.
• Because the sort criteria are stored as part of the query, the query will always be properly sorted each time you run it.

The disadvantages of sorting the result using an ORDER BY clause are:

• You are asking the database server to do additional work, which may affect the speed of other applications that are trying to use the database server.
• If you are creating an ad hoc query, and don't know if you need all of the rows of the result, you still need to wait until they are retrieved and sorted before you can see any of the rows of the result.

This topic teaches you how to use SQLWizard's Query Design window to add an ORDER BY clause to your query.

To create an ORDER BY clause on a single column:

1. Add the column you want to sort on to the Query Conditions Grid. If you do not want to see the column in the result, still add it to the grid, and then double click the title to hide it in the result.
2. Click the title of the column to select the column.
3. Click the Sort Column command from the Column menu.
4. Choose Ascending or Descending from the Sort Column submenu. Ascending means the columns are sorted from 0 to 9 and A to Z (or as appropriate for international versions). Descending means the columns are sorted from 9 to 0 and Z to A (or as appropriate for international versions).
   Note: You can repeat the process above as many times as you like. Each time you add another column, it is appended to the ORDER BY clause. To see or modify all of the columns in the ORDER BY clause, switch to the SQL view, or follow the instructions for creating an ORDER BY clause on multiple columns.

To create an ORDER BY clause on multiple columns:

1. Add all of the columns that you want to see in the result to the Query Conditions Grid.
2. If there are columns that you do not want to see in the result, but that you want to use for ordering the result, add them to the Query Conditions Grid as well. For these columns, double click the column name to exclude the column from the columns displayed in the query result.
3. Click the Sort command from the Query menu or use the Sort button on the toolbar.

   SQLWizard displays the Order By dialog box. This dialog box contains two lists: one containing all of the columns in the Query Conditions Grid, and the other containing the columns that will be included in the ORDER BY clause used to sort the result records. When you display the Order By dialog box for the first time, the list of Sorted columns is empty.

4. To add a column to the ORDER BY clause, locate the column in the Unsorted Columns list and click it to select it. Then click the > button to move the name from the Unsorted Columns list to the Sorted Columns list. You can remove a column from the Sort Columns list by selecting it and using the < key. The >> button moves all columns in the Unsorted Columns list to the Sorted Columns list, while the << button moves all columns from the Sorted Columns list to the Unsorted Columns list.
5. Once you have picked the columns you want to use for ordering the result, you can rearrange the order in which they appear in the ORDER BY clause. The first column in the ORDER BY clause is the primary sort column. Each additional column sorts the records in the group defined by the previous column. To move a column up in the list, click it and use the Up or Down buttons.
   Note: The first column in the list is the most significant column. Data is first sorted by this column, and each of the other columns are applied to the sorted rows in turn.
6. You can also specify whether an ascending or descending sort sequence is to be used in sorting by a particular field. Ascending sorts go from 0-9 and A-Z (or as appropriate for international versions). Descending sort sequences go from 9-0 and Z-A (or as appropriate for international versions). To toggle between Ascending and Descending, click the arrow buttons beside a column name in the Sorted Columns list.

   
   
   indicates ascending order.

   
   
   indicates descending order.

7. Once you are satisfied with the Sorted Columns list, click OK to close the dialog box.
8. Now examine the Query Conditions Grid. You now see a new row labeled ORDER/GROUP, containing numbers and icons. The number indicates the order of the column in the ORDER BY clause, and the
   
   or
   
   symbol indicates whether the column is to be sorted in ascending or descending sequence.
9. You can change the ORDER BY clause by redisplaying the Order By dialog box and modifying the Sorted Columns list as described above. Or you can click the ORDER/GROUP row and change the number and ascending/descending specification.