Tuesday, June 10, 2014

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

No comments: