Sorting on derived columns

When you want to use ORDER BY on an expression, you can use either the display label assigned to the expression or an integer, as Figure 1 and Figure 3 show.

Figure 1. Query

SELECT customer_num, call_code, call_dtime, 
       res_dtime - call_dtime span
   FROM cust_calls
   ORDER BY span;

The query retrieves the same data from the cust_calls table as Figure 5. In the query, the ORDER BY clause causes the data to be displayed in ascending order of the derived values in the span column, as the result shows.

Figure 2. Query result

customer_num call_code call_dtime               span

         127 I         1998-07-31 14:30
         121 O         1998-07-10 14:05         0 00:01
         106 D         1998-06-12 08:20         0 00:05
         110 L         1998-07-07 10:24         0 00:06
         116 I         1997-11-28 13:34         0 03:13
         119 B         1998-07-01 15:00         0 17:21
         116 I         1997-12-21 11:24         5 20:55

The following query uses an integer to represent the result of the operation res_dtime - call_dtime and retrieves the same rows that appear in the above result.

Figure 3. Query

SELECT customer_num, call_code, call_dtime, 
       res_dtime - call_dtime span
   FROM cust_calls
   ORDER BY 4;