Ordering by a CASE expression

The ORDER BY clause can include CASE expressions to specify a sorting key.

In the following example, column a_col of table tab_case is of type INT. The query on table tab_case includes both column a_col and the aggregate expression SUM(a_col) in the Projection list, and groups the results by the value of a_col. The ORDER BY clause specifies two sorting keys:
  • A CASE expression that immediately follows the ORDER BY keywords
  • The AVG(a_col) aggregate expression:
CREATE TABLE tab_case(a_col INT, b_col VARCHAR(32));

SELECT  a_col, SUM(a_col) 
   FROM tab_case 
   GROUP BY a_col 
   ORDER BY CASE 
               WHEN a_col IS NULL 
                  THEN 1 
               ELSE 0 END ASC, 
            AVG(a_col);
Here the ASC keyword explicitly identifies the result of the CASE expression as an ascending sort key. By default, the AVG(a_col) sorting key also specifies an ascending order.

In the following similar example, based on a query on the same tab_case table, a second CASE expression returns either 1 or 0 as the sorting key value for the returned AVG(a_col) aggregate values.

SELECT  a_col, SUM(a_col) 
   FROM tab_case GROUP BY a_col 
   ORDER BY CASE
               WHEN a_col IS NULL 
                  THEN 1 
               ELSE 0 END ASC,
            AVG(a_col), 
            CASE 
               WHEN AVG(a_col) IS NULL 
                  THEN 1 
               ELSE 0 END;