PostgreSQL - ORDER BY Clause

PostgreSQL - The ORDER BY Clause

The PostgreSQL ORDER BY clause is essential for sorting the rows returned by a SELECT query. You can sort the data based on one or more columns in either ascending or descending order, which is crucial for creating organized reports, presenting data to users, and for certain types of data analysis.

Syntax

The basic syntax of the ORDER BY clause is as follows:

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

• column1, column2, etc., are the names of the columns you want to sort by.
• ASC (Ascending) is the default sort order, from smallest to largest. You can omit it.
• DESC (Descending) sorts from largest to smallest.
• You can sort by multiple columns. The query first sorts by column1, then for rows with the same column1 value, it sorts them by column2, and so on.

Setup: Sample Data

To demonstrate the ORDER BY clause, let’s create and populate a sample employees table. This will provide a consistent dataset for our examples.

-- Create the table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date DATE
);

-- Insert some data
INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
('James', 'Smith', 'Sales', 55000.00, '2021-10-01'),
('Maria', 'Garcia', 'HR', 60000.00, '2020-05-15'),
('David', 'Smith', 'Engineering', 80000.00, '2022-01-20'),
('John', 'Johnson', 'Engineering', 95000.00, '2019-03-12'),
('Patricia', 'Williams', 'Sales', 58000.00, '2021-10-01'),
('Robert', 'Jones', 'Engineering', 80000.00, '2022-02-11'),
('Jennifer', 'Brown', 'HR', NULL, '2023-01-09'); -- Note the NULL salary

Example 1: Single-Column Sorting

Let’s sort all employees by their salary in descending order (highest first).

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

This query produces the following result:

 first_name | last_name |  salary
------------+-----------+----------
 John       | Johnson   | 95000.00
 David      | Smith     | 80000.00
 Robert     | Jones     | 80000.00
 Maria      | Garcia    | 60000.00
 Patricia   | Williams  | 58000.00
 James      | Smith     | 55000.00
 Jennifer   | Brown     |   NULL
(7 rows)

Example 2: Multi-Column Sorting

Now, let’s sort by department in ascending order, and for employees in the same department, sort by salary in descending order.

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

This gives a more structured view, grouping departments together:

 department  | first_name |  salary
-------------+------------+----------
 Engineering | John       | 95000.00
 Engineering | David      | 80000.00
 Engineering | Robert     | 80000.00
 HR          | Maria      | 60000.00
 HR          | Jennifer   |   NULL
 Sales       | Patricia   | 58000.00
 Sales       | James      | 55000.00
(7 rows)

Advanced Topic: Handling NULL Values

By default, PostgreSQL treats NULL values as larger than any other value. This means in an ascending (ASC) sort, NULLs appear last, and in a descending (DESC) sort, they appear first. You can override this behavior using NULLS FIRST or NULLS LAST.

For example, to sort by salary descending but ensure employees with no salary information appear at the very end:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC NULLS LAST;

Result:

 first_name | last_name |  salary
------------+-----------+----------
 John       | Johnson   | 95000.00
 David      | Smith     | 80000.00
 Robert     | Jones     | 80000.00
 Maria      | Garcia    | 60000.00
 Patricia   | Williams  | 58000.00
 James      | Smith     | 55000.00
 Jennifer   | Brown     |   NULL
(7 rows)

Performance Considerations

Sorting can be an expensive operation, especially on large tables. If you frequently sort by a specific column or set of columns, creating an index on them can significantly improve query performance. The database can use the pre-sorted index instead of sorting the entire result set on the fly.

-- This index would speed up the multi-column sorting example above
CREATE INDEX idx_employees_dept_salary ON employees (department ASC, salary DESC);

Best Practices

• Be explicit: Always specify ASC or DESC to make your queries clearer, even though ASC is the default.
• Limit your results: Sorting a huge dataset is slow. Combine ORDER BY with LIMIT to sort and retrieve only the top N results, which is a very common and efficient pattern.
• Index for performance: For frequently sorted columns in large tables, create an index to avoid costly full-table sorts.