PostgreSQL: Sorting Data with the ORDER BY Clause

Sort query results in PostgreSQL using the ORDER BY clause. This guide explains ascending and descending sorting and sorting by multiple columns.

Sorting Data in PostgreSQL with the `ORDER BY` Clause

The PostgreSQL `ORDER BY` clause lets you sort the rows returned by a `SELECT` statement. This is essential for organizing and presenting data in a meaningful way. You can sort by one or more columns in either ascending or descending order.

Understanding the `ORDER BY` Clause

By default, `SELECT` statements return rows in an undefined order. The `ORDER BY` clause lets you specify how you want the results sorted. You can sort by one or more columns, and for each column, you can specify ascending (`ASC`) or descending (`DESC`) order.

`ORDER BY` Clause Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

    

The `ORDER BY` clause comes after the `FROM` clause and before any `LIMIT` or `OFFSET` clauses.

column1, column2,...: The columns to sort by.
ASC (ascending): Sorts from smallest to largest (default if not specified).
DESC (descending): Sorts from largest to smallest.

Examples

(Note: These examples assume the existence of an `employee` table with appropriate columns. Screenshots in the original text are not included here. Please refer to the original document for the visual examples. The descriptions below aim to convey the information in those screenshots.)

Example 1: Sorting by a Single Column (Ascending)

SELECT first_name, last_name 
FROM employees
ORDER BY first_name;  -- ASC is the default

    

Example 2: Sorting by a Single Column (Descending)

SELECT first_name, last_name 
FROM employees
ORDER BY last_name DESC;

    

Example 3: Sorting by Multiple Columns

SELECT address, email 
FROM employees
ORDER BY address ASC, email DESC;

    

Example 4: Sorting by an Expression

SELECT address, LENGTH(address) AS address_length
FROM employees
ORDER BY address_length DESC;

    

Handling NULL Values

To control the ordering of `NULL` values, use the `NULLS FIRST` or `NULLS LAST` clauses:

ORDER BY column_name ASC NULLS FIRST;  -- NULLs at the beginning
ORDER BY column_name DESC NULLS LAST;   -- NULLs at the end

(Note: This section includes several screenshots showing `psql` output for creating a table, inserting data including `NULL` values, and running `SELECT` queries with different `ORDER BY` clauses. Since images cannot be displayed here, please refer to the original document for visual confirmation of the examples. The descriptions below aim to convey the information present in those screenshots.)

The examples demonstrate sorting with `NULLS FIRST` and `NULLS LAST` options, showing how to control the placement of `NULL` values in the sorted results.

Follow On

TutorialsArena