Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL ORDER BY

The `ORDER BY` clause in PostgreSQL is used to sort the result set of a query by one or more columns. It can sort data in ascending (default) or descending order to make data analysis and visualization more intuitive.

Usage

The `ORDER BY` clause is employed when you need to organize query results in a specific order. This clause follows the `SELECT` statement and can be combined with other clauses like `WHERE` and `GROUP BY`.


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

In this syntax, `ORDER BY column1 [ASC|DESC]` specifies the column used for sorting and the order of sorting, which can be either ascending (`ASC`) or descending (`DESC`).

Examples

1. Basic Ordering


SELECT name, age
FROM users
ORDER BY age;

This example sorts the `users` table by the `age` column in ascending order, which is the default.

2. Descending Order


SELECT product_name, price
FROM products
ORDER BY price DESC;

Here, the `products` table is sorted by the `price` column in descending order, displaying the most expensive products first.

3. Multiple Column Ordering


SELECT employee_id, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

This example sorts the `employees` table first by `department` in ascending order and then by `salary` within each department in descending order.

Tips and Best Practices

  • Specify clear sorting rules. Use `ASC` or `DESC` to explicitly define sorting order for each column.
  • Use multiple columns wisely. When ordering by multiple columns, ensure the sequence makes logical sense for your analysis.
  • Optimize performance. Consider indexing the columns used in `ORDER BY` for better query performance, especially with large datasets.
  • Combine with `LIMIT`. Use `ORDER BY` in conjunction with `LIMIT` to efficiently fetch top or bottom records.
  • Handle `NULL` values appropriately. By default, `NULL` values are sorted as if they are higher than non-`NULL` values in ascending order and lower in descending order. Use `NULLS FIRST` or `NULLS LAST` to explicitly specify the position of `NULL` values.
  • Performance considerations. When using `ORDER BY` with large datasets, be aware that sorting can be resource-intensive. Indexing the columns involved in sorting can significantly improve performance.