ORDER BY an Expression

Sometimes a program requires that the results of a query be returned in a specific sequence. We all know that the ORDER BY clause can be used to sort SQL results into a specific order. For example, to return a sorted list of employee compensation sorted by last name we could write:

SELECT LASTNAME, 

       FIRSTNAME, 

       SALARY+COMM+BONUS

FROM   EMP

ORDER BY LASTNAME;

But what if we need to sort it by total compensation? There are two approaches that work here: position number and column renaming. Using position number the ORDER BY clause becomes:

SELECT LASTNAME, 

       FIRSTNAME, 

       SALARY+COMM+BONUS

FROM   EMP

ORDER BY 3;

This will cause DB2 to sort by the third element in the SELECT-list, in this case the total compensation expression. But what if we add another column at the beginning of the SELECT-list? Or what if we need to port the SQL to a different database with different standards? Well, in that case we can use column renaming:

SELECT LASTNAME, 

       FIRSTNAME, 

       SALARY+COMM+BONUS AS TOTAL_COMP

FROM   EMP

ORDER BY TOTAL_COMP;

This method is preferred for a number of reasons:

• it will continue to work even if the SQL statement is changed
• it gives the expression a name making it more self-documenting
• it should be more portable