PostgreSQL ORDER BY - Sort the result set

In PostgreSQL, the ORDER BY clause is used to sort the result set returned by the SELECT statement. If you do not specify a ORDER BY clause for the SELECT statement, the result set is ordered according to the default rules of the database.

PostgreSQL `ORDER BY` Syntax

To sort the result set returned by the SELECT statement in ascending or descending order by one or more of these columns, use the following syntax of the ORDER BY clause:

SELECT
   column1, column2, ...
FROM
   table_name
[WHERE clause]
ORDER BY
   column1 [ASC|DESC],
   [column2 [ASC|DESC],
   ...]
   [NULLS FIRST | NULLS LAST]
   ;

Explanation:

You can specify one or more columns or expressions for the ORDER BY clause .
The ASC for ascending order, and the DESC for descending order. This is optional, the default is ASC.
The NULLS FIRST and the NULLS LAST are used to specify collation for null values:
- NULLS FIRST: null values precede non-null values.
- NULLS LAST: null values follow non-null values.
By default, PostgreSQL uses NULLS LAST if sorting in ascending order, and NULLS FIRST if sorting indescending order. That is, PostgreSQL defaults to null values is larger than non-null values.

PostgreSQL `ORDER BY` collation description

The following describes the sorting rules in different cases:

ORDER BY column ASC;

This ORDER BY clause sorts the result set in ascending order by the value of the column column.
ORDER BY column DESC;

This ORDER BY clause sorts the result set in descending order by the value of the column column.
ORDER BY column;

This ORDER BY clause sorts the result set in ascending order by the value of the column column. This statement is equivalent to: ORDER BY column ASC;.
ORDER BY column1, column2;

This ORDER BY clause sorts the result set by column1 column value in ascending order first, and then by column2 column value in ascending order.

That is to say, the main sort is in ascending order by column1 column, and on the basis of the main sort, those rows with the same column1 value are sorted in ascending order by column2 column.
ORDER BY column1 DESC, column2;

This ORDER BY clause sorts the result set by column1 column values in descending order first, and then by column2 column values in ascending order.

That is to say, the main sort is in descending order by column1 column, and on the basis of main sort, those rows with the same column1 value are sorted in ascending order by column2 column.

PostgreSQL `ORDER BY` Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the following example, the actor’s information is stored in the actor table.

Sort by a column in ascending order

The following SQL statement uses the ORDER BY clause to sort actors by last name in ascending order.

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name;

 actor_id | first_name  |  last_name
----------+-------------+--------------
      182 | DEBBIE      | AKROYD
       92 | KIRSTEN     | AKROYD
       58 | CHRISTIAN   | AKROYD
      194 | MERYL       | ALLEN
      145 | KIM         | ALLEN
      118 | CUBA        | ALLEN
       76 | ANGELINA    | ASTAIRE
      112 | RUSSELL     | BACALL
      190 | AUDREY      | BAILEY
       67 | JESSICA     | BAILEY
      115 | HARRISON    | BALE
      187 | RENEE       | BALL
...
(200 rows)

Sort by a column in descending order

The following SQL statement uses the ORDER BY clause to sort actors by last name in descending order.

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name DESC;

 actor_id | first_name  |  last_name
----------+-------------+--------------
      186 | JULIA       | ZELLWEGER
      111 | CAMERON     | ZELLWEGER
       85 | MINNIE      | ZELLWEGER
       63 | CAMERON     | WRAY
       13 | UMA         | WOOD
      156 | FAY         | WOOD
      144 | ANGELA      | WITHERSPOON
       68 | RIP         | WINSLET
      147 | FAY         | WINSLET
      168 | WILL        | WILSON
      164 | HUMPHREY    | WILLIS
       96 | GENE        | WILLIS
...
(200 rows)

Sort by multiple columns

The following SQL statement uses the ORDER BY clause sort actors by last name in ascending order first, and then by first name in ascending order.

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name, first_name;

 actor_id | first_name  |  last_name
----------+-------------+--------------
       58 | CHRISTIAN   | AKROYD
      182 | DEBBIE      | AKROYD
       92 | KIRSTEN     | AKROYD
      118 | CUBA        | ALLEN
      145 | KIM         | ALLEN
      194 | MERYL       | ALLEN
       76 | ANGELINA    | ASTAIRE
      112 | RUSSELL     | BACALL
      190 | AUDREY      | BAILEY
       67 | JESSICA     | BAILEY
      115 | HARRISON    | BALE
      187 | RENEE       | BALL
...
(200 rows)

Sort by custom order

Sometimes simply sorting by the value of the column does not meet the requirements, we need to sort in a custom order. For example, we need to sort films by ratings in the order of 'G', 'PG', 'PG-13', 'R', 'NC-17'.

For such a requirement, it can be understood as sorting by the index position of the elements in the list. We implement it using the CASE clause.

In the following examples, we use film tables for demonstration.

Suppose you want to sort films according to their ratings in the order of 'G', 'PG', 'PG-13', 'R', 'NC-17'.

SELECT
    film_id, title, rating
FROM
    film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;


     357 | GILBERT PELICAN             | G
     597 | MOONWALKER FOOL             | G
     354 | GHOST GROUNDHOG             | G
...
     595 | MOON BUNCH                  | PG
       6 | AGENT TRUMAN                | PG
     600 | MOTIONS DETAILS             | PG
...
       9 | ALABAMA DEVIL               | PG-13
     657 | PARADISE SABRINA            | PG-13
     956 | WANDA CHAMBER               | PG-13
...
     749 | RULES HUMAN                 | R
       8 | AIRPORT POLLOCK             | R
      17 | ALONE TRIP                  | R
...
     520 | LICENSE WEEKEND             | NC-17
     517 | LESSON CLEOPATRA            | NC-17
     114 | CAMELOT VACATION            | NC-17
...
(1000 rows)

In this example, we use the CASE clause to convert each film’s rating to a number， and then the ORDER BY sort films by this number.

ORDER BY and NULL

In ascending sorts in PostgreSQL, NULL values appear before non-NULL values.

This below example uses the following temporary data as a demonstration:

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT '0' AS v
UNION ALL
SELECT '1' AS v;

   v
--------
 A
 B
 <null>
 0
 1
(5 rows)

When we sort them in ascending order, null values are follow non-null values by default, as follows:

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT '0' AS v
UNION ALL
SELECT '1' AS v
ORDER BY v;

   v
--------
 0
 1
 A
 B
 <null>

Here, the ascending order uses the NULLS LAST rule by default, so the null value is last. If you want null values are sorted ahead, execute the following statement:

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT '0' AS v
UNION ALL
SELECT '1' AS v
ORDER BY v NULLS FIRST;

   v
--------
 <null>
 0
 1
 A
 B

When we sort them in descending order, the null values come before the non-values, as follows:

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT '0' AS v
UNION ALL
SELECT '1' AS v
ORDER BY v DESC;

   v
--------
 <null>
 B
 A
 1
 0

Conclusion

PostgreSQL ORDER BY clause is used to sort the result set returned by the SELECT statement. If you do not specify a ORDER BY clause for the SELECT statement, the result set is ordered according to the default rules of the database.

The key points of the usage of the ORDER BY clause are as follows:

Use the ORDER BY clause to sort the result set by one or more columns.
Use ASC to sort the result set in ascending order, and use DESC to sort the result set in descending order.
Use NULLS FIRST or NULLS LAST to change the processing rules for null values.

PostgreSQL ORDER BY Syntax

PostgreSQL ORDER BY collation description

PostgreSQL ORDER BY Examples