ORDER BY

Sorts rows in the output table.

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

The sort_expression(s) can be any expression valid in a select list e.g. col1 + col2. When more than one expression is specified, the later values are used to sort rows that are equal according to the earlier values.

Without sorting, rows are returned in an unspecified order (no guarantee):

mydb=> SELECT * FROM drinks;
   name   | unit_price | serving_temp 
----------+------------+--------------
 Tea      |      $9.99 | hot
 Lemonade |      $5.50 | cool
 Milk     |      $7.50 | cool
 Coffee   |            | hot
 Tea      |      $9.50 | warm
(5 rows)

ASC and DESC keywords set the sort direction to ascending and descending respectively:

mydb=> SELECT * FROM drinks ORDER BY name, serving_temp;  -- ASC is default
   name   | unit_price | serving_temp 
----------+------------+--------------
 Coffee   |            | hot
 Lemonade |      $5.50 | cool
 Milk     |      $7.50 | cool
 Tea      |      $9.50 | warm
 Tea      |      $9.99 | hot
(5 rows)

mydb=> SELECT * FROM drinks ORDER BY name DESC, serving_temp DESC;
   name   | unit_price | serving_temp 
----------+------------+--------------
 Tea      |      $9.99 | hot
 Tea      |      $9.50 | warm
 Milk     |      $7.50 | cool
 Lemonade |      $5.50 | cool
 Coffee   |            | hot
(5 rows)

Note

Ordering options are considered independently for each sort colums: ORDER BY col1, col2 DESC means ORDER BY col1 ASC, col2 DESC.

NULLS FIRST and NULLS LAST options can be used to determine whether nulls appear before or after non-null values. By default, null values sort as if larger than any non-null value. So, NULLS FIRST is default in DESC; NULLS LAST in ASC.

mydb=> SELECT * FROM drinks ORDER BY unit_price DESC;
   name   | unit_price | serving_temp 
----------+------------+--------------
 Coffee   |            | hot
 Tea      |      $9.99 | hot
 Tea      |      $9.50 | warm
 Milk     |      $7.50 | cool
 Lemonade |      $5.50 | cool
(5 rows)

mydb=> SELECT * FROM drinks ORDER BY unit_price DESC NULLS LAST;
   name   | unit_price | serving_temp 
----------+------------+--------------
 Tea      |      $9.99 | hot
 Tea      |      $9.50 | warm
 Milk     |      $7.50 | cool
 Lemonade |      $5.50 | cool
 Coffee   |            | hot
(5 rows)

A sort expression can also be the alias or number of an output column:

mydb=> SELECT initcap(serving_temp::text) || ' ' || name AS drink, unit_price
mydb->   FROM drinks
mydb->   ORDER BY drink, 2;
     drink     | unit_price 
---------------+------------
 Cool Lemonade |      $5.50
 Cool Milk     |      $7.50
 Hot Coffee    |           
 Hot Tea       |      $9.99
 Warm Tea      |      $9.50
(5 rows)