InterBase Quick Start: Part IV - Grouping and Ordering Query Results

Go Up to InterBase Quick Start: Part IV - Retrieving Data

Rows are not stored in any particular order in a database. So when you execute a query, you may find that the results are not organized in any useful way. The ORDER BY clause lets you specify how the returned rows should be ordered. You can use the GROUP BY clause to group the results of aggregate functions.

Using ORDER BY to Arrange Rows

You can use the ORDER BY clause to organize the data that is returned from your queries. You can specify one or more columns by name or by ordinal number. The syntax of the ORDER BY clause is:

 ORDER BY [col_name | int] [ASC[ENDING] | DESC[ENDING]] [, …]

Notice that you can specify more than one column. As an alternative to naming the columns, you can provide an integer that references the order in which you named the columns in the query. The second column that you named in the SELECT can be referenced as 2.

By default, InterBase uses ASCENDING order, so you only need to specify the order if you want it to be DESCENDING.

Practicing with ORDER BY

1. Execute the following query (you did this one earlier when you worked with conditions for subqueries).

   SELECT cust_no, total_value FROM Sales  
          WHERE total_value > 10000
   

   There is no particular order to the returned rows. The result set should look like this:

   

2. Execute the same query, but order the results by the cust_no column:

   SELECT cust_no, total_value FROM Sales  
          WHERE total_value > 10000  
         ORDER BY cust_no
   

   Now the result has the cust_no column in ascending order. Ascending order is the default.

3. Order the result set by the total value of the sales:

    SELECT cust_no, total_value FROM Sales  
          WHERE total_value > 10000  
          ORDER BY total_value
   

4. Execute the query above, but order the result set by the descending order of the total_value column:

      SELECT cust_no, total_value FROM Sales  
          WHERE total_value > 10000  
          ORDER BY total_value DESC
   

5. To see the effect of ordering by more than one column, execute the following query:

     SELECT last_name, first_name, phone_ext FROM Employee  
          ORDER BY last_name DESC, first_name
   

   Notice that there are 42 rows with the last names are in descending order, as requested, and the first names in ascending order, the default.

Advance To:

• Using the GROUP BY Clause