Using ORDER BY to Arrange Rows

From InterBase
Jump to: navigation, search

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

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.

Image 025.jpg 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:
TableQuery20.png

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
Notice that the result set now 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: