PostgreSQL ORDER BY: Sorting Rows in a Query

Summary: in this tutorial, you’ll learn how to use the PostgreSQL ORDER BY clause in the SELECT statement to sort rows in ascending or descending orders.

Introduction to PostgreSQL ORDER BY clause #

The SELECT statement returns a result set with rows in an unspecified order by default. To sort rows returned by the SELECT statement, you use the ORDER BY clause.

Here’s the syntax of the ORDER BY clause:

SELECT
  column1,
  column2,
  ...
FROM
  table_name
ORDER BY
  sort_expression [ASC | DESC]; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the table name from which you want to retrieve data in the FROM clause.
Second, provide the columns to include in the result set in the SELECT clause.
Third, specify the sort expression in the ORDER BY clause.

The sort_expression can be a column or an expression for sorting.

Use ASC to sort rows in ascending order and DESC to sort rows in descending order.

ASC or DESC option is optional. The ORDER BY clause uses the ASC by default if you don’t explicitly specify it.

PostgreSQL evaluates the clauses in the SELECT statement in the following orders:

FROM
SELECT
ORDER BY

If you have column aliases in the SELECT clause, you can use them in the ORDER BY clause. The reason is that PostgreSQL evaluates the SELECT clause before the ORDER BY clause. The column aliases that are available in the SELECT clause are accessible by the ORDER BY clause.

Setting up a sample table #

We’ll create a new table, inventories, to practice with the ORDER BYclause:

SQL Script to create and insert some rows into the inventories table:

CREATE TABLE inventories (
    name VARCHAR(255) NOT NULL,
    price DEC(11,2) NOT NULL,
    quantity INT NOT NULL,
    color VARCHAR(50),
    updated_date DATE NOT NULL    
);

INSERT INTO inventories (name, price, quantity, color, updated_date) 

VALUES
('iPhone 15 Black', 999.99, 10, 'Black', '2024-12-01'),
('Galaxy S23', 899.99, 12, NULL, '2024-12-02'),
('Pixel 8', 799.99, 7, NULL, '2024-12-02'),
('iPhone 15 Pro', 1099.99, 6, 'Silver', '2024-12-03'),
('Galaxy S23 Ultra', 1199.99, 4, 'Black', '2024-12-03'),
('Pixel 8 Pro', 999.99, 9, 'Red', '2024-12-03'),
('iPhone 15 Pro Max', 1299.99, 3, 'Gold', '2024-12-04');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Sorting rows by one column #

The following statement uses the ORDER BY clause to sort the product in the inventory by price from lowest to highest:

SELECT
  name,
  price
FROM
  inventories
ORDER BY
  price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        |  price
-------------------+---------
 Pixel 8           |  799.99
 Galaxy S23        |  899.99
 Pixel 8 Pro       |  999.99
 iPhone 15 Black   |  999.99
 iPhone 15 Pro     | 1099.99
 Galaxy S23 Ultra  | 1199.99
 iPhone 15 Pro Max | 1299.99Code language: plaintext (plaintext)

To sort the products by price in descending order, you use the DESC option:

SELECT
  name,
  price
FROM
  inventories
ORDER BY
  price DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        |  price
-------------------+---------
 iPhone 15 Pro Max | 1299.99
 Galaxy S23 Ultra  | 1199.99
 iPhone 15 Pro     | 1099.99
 iPhone 15 Black   |  999.99
 Pixel 8 Pro       |  999.99
 Galaxy S23        |  899.99
 Pixel 8           |  799.99Code language: plaintext (plaintext)

Sorting rows by multiple columns #

Some products control the same price; you can sort the products by price first and then sort the sorted product list by names.

The following query uses the ORDER BY clause to sort products by values in the price and name columns:

SELECT
  name,
  price
FROM
  inventories
ORDER BY
  price DESC,
  name DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        |  price
-------------------+---------
 iPhone 15 Pro Max | 1299.99
 Galaxy S23 Ultra  | 1199.99
 iPhone 15 Pro     | 1099.99
 Pixel 8 Pro       |  999.99
 iPhone 15 Black   |  999.99
 Galaxy S23        |  899.99
 Pixel 8           |  799.99Code language: plaintext (plaintext)

Sorting rows by expressions #

The following statement calculates the inventory amount of each product and sorts the products by inventory amount from high to low:

SELECT
  name,
  price * quantity
FROM
  inventories
ORDER BY
  price * quantity DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        | ?column?
-------------------+----------
 Galaxy S23        | 10799.88
 iPhone 15 Black   |  9999.90
 Pixel 8 Pro       |  8999.91
 iPhone 15 Pro     |  6599.94
 Pixel 8           |  5599.93
 Galaxy S23 Ultra  |  4799.96
 iPhone 15 Pro Max |  3899.97Code language: plaintext (plaintext)

Since PostgreSQL evaluates the SELECT statement before the ORDER BY statement, you can use a column alias in the SELECT and ORDER BY clauses:

SELECT
  name,
  price * quantity AS amount
FROM
  inventories
ORDER BY
  amount DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        |  amount
-------------------+----------
 Galaxy S23        | 10799.88
 iPhone 15 Black   |  9999.90
 Pixel 8 Pro       |  8999.91
 iPhone 15 Pro     |  6599.94
 Pixel 8           |  5599.93
 Galaxy S23 Ultra  |  4799.96
 iPhone 15 Pro Max |  3899.97Code language: plaintext (plaintext)

In this example, we assign the amount as the column alias for the expression price * quantity and then use it in the ORDER BY clause for sorting.

Sorting rows by dates #

The following statements use the ORDER BYclause to sort rows in the inventories table by updated dates from the earliest to the latest:

SELECT
  name,
  updated_date
FROM
  inventories
ORDER BY
  updated_date;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        | updated_date
-------------------+--------------
 iPhone 15 Black   | 2024-12-01
 Galaxy S23        | 2024-12-02
 Pixel 8           | 2024-12-02
 iPhone 15 Pro     | 2024-12-03
 Galaxy S23 Ultra  | 2024-12-03
 Pixel 8 Pro       | 2024-12-03
 iPhone 15 Pro Max | 2024-12-04Code language: plaintext (plaintext)

You can sort products in the inventory by updated date from latest to earliest by using the DESC option as follows:

SELECT
  name,
  updated_date
FROM
  inventories
ORDER BY
  updated_date DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        | updated_date
-------------------+--------------
 iPhone 15 Pro Max | 2024-12-04
 iPhone 15 Pro     | 2024-12-03
 Galaxy S23 Ultra  | 2024-12-03
 Pixel 8 Pro       | 2024-12-03
 Galaxy S23        | 2024-12-02
 Pixel 8           | 2024-12-02
 iPhone 15 Black   | 2024-12-01Code language: plaintext (plaintext)

PostgreSQL ORDER BY: Dealing with NULLs in sorting #

In PostgreSQL, NULL means unknown or missing data. Since NULL is unknown, you cannot compare it with any other values.

However, PostgreSQL needs to know which values are before or after other values to perform sorting. When it comes to NULL, PostgreSQL provides two options in the ORDER BY clause:

ORDER BY sort_expression NULLS FIRST;
ORDER BY sort_expression NULLS LAST;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

NULLS FIRST places NULLs before other non-NULL values.
NULLS LAST places NULLs after other non-NULL values.

Note that between the sort expression and NULLS FIRST or NULLS LAST, you can use the ASC or DESC option.

The following statement uses the ORDER BY clause to sort the products by color alphabetically, placing NULLs first:

SELECT
  name,
  color
FROM
  inventories
ORDER BY
  color NULLS FIRST;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        | color
-------------------+--------
 Galaxy S23        | NULL
 Pixel 8           | NULL
 iPhone 15 Black   | Black
 Galaxy S23 Ultra  | Black
 iPhone 15 Pro Max | Gold
 Pixel 8 Pro       | Red
 iPhone 15 Pro     | SilverCode language: plaintext (plaintext)

To place NULLs after other non-NULL values, you can use the NULLS LAST option:

SELECT
  name,
  color
FROM
  inventories
ORDER BY
  color NULLS LAST;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        | color
-------------------+--------
 Galaxy S23 Ultra  | Black
 iPhone 15 Black   | Black
 iPhone 15 Pro Max | Gold
 Pixel 8 Pro       | Red
 iPhone 15 Pro     | Silver
 Pixel 8           | NULL
 Galaxy S23        | NULLCode language: plaintext (plaintext)

Summary #

Use the ORDER BY clause in the SELECT statement to sort rows by one or more columns.
PostgreSQL evaluates the ORDER BY clause after the SELECT clause.
The ASC option orders rows from low to high, while the DESC option orders from high to low.
The NULLS FIRST places NULLs before, and NULLS LAST puts NULLs after other non-NULL values.

Quiz #

Was this tutorial helpful ?