Database Toolbox

Presenting Results in Specified Order

By default, the order of the rows in the query results depends on their order in the database, which is effectively random. Use the Order by field in Advanced query options to specify the order in which results appear. This example uses the basic_where.qry query, which was created and saved in the example presented in Retrieving Information That Meets Specified Criteria.

This example sorts the results of basic_where.qry, so that January is the primary sort field, February the secondary, and March the last. Results for January and February are ascending, and results for March are descending:

1. Load basic_where.qry. For instructions, see Using a Saved Query.
2. Set Preferences. For this example, set Data return format to cellarray and Read NULL numbers as to NaN.
3. In Advanced query options, click Order by.

1. The Order By Clauses dialog box appears.

   

4. For the Fields whose results you want to specify the order of, specify the Sort key number and Sort order. For example, specify January as the primary sort field, with results displayed in ascending order.
1. From Fields, select January.
2. For Sort key number, type 1.
3. For Sort order, select Ascending.
4. Click Apply.

• The Current clauses area now shows

  January ASC

5. Specify February as the second sort field, with results displayed in ascending order.
1. From Fields, select February.
2. For Sort key number, type 2.
3. For Sort order, select Ascending.
4. Click Apply.

• The Current clauses area now shows

  January ASC
February ASC

6. Specify March as the third sort field, with results displayed in descending order.
1. From Fields, select March.
2. For Sort key number, type 3.
3. For Sort order, select Descending.
4. Click Apply.

• The Current clauses area now shows

  January ASC
February ASC
March DESC

7. Click OK.

1. The Order By Clauses dialog box closes. The Order by field and the SQL statement in the Visual Query Builder reflect the order by clause you specified.

8. Assign a MATLAB workspace variable, for example, B.
9. Click Execute.
10. To view the results, type B in the Command Window. Compare these to the unordered query results, shown as A.

    

1. For B, results are first sorted by January sales, in ascending order. The lowest value for January sales, 1200 (for item number 400455) appears first and the highest value, 5000 (for item number for 400345) appears last.

   For items 400999, 400314, and 400876, January sales were equal at 3000. Therefore, the second sort key, February sales, applies. February sales appear in ascending order--1500, 2400, and 2400 respectively.

   For items 400314 and 400876, February sales were 2400, so the third sort key, March sales, applies. March sales appear in descending order--1800 and 1500 respectively.

Grouping Criteria

Creating Subqueries for Values from Multiple Tables

© 1994-2005 The MathWorks, Inc.