PostgreSQL ORDER BY Clause

The ORDER BY clause in PostgreSQL is used to sort the result set of a query. Sorting can be done in ascending (ASC) or descending (DESC) order. This is especially useful when analyzing, reporting, or presenting data in a meaningful sequence.

1. Basic Syntax

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

- column1, column2 → columns to sort by - ASC → ascending order (default) - DESC → descending order

2. Example: Sorting by a Single Column

-- Sort employees by salary in ascending order
            SELECT name, department, salary
            FROM employees
            ORDER BY salary ASC;

            -- Sort employees by salary in descending order
            SELECT name, department, salary
            FROM employees
            ORDER BY salary DESC;

3. Sorting by Multiple Columns

You can sort by more than one column to control tie-breaking situations.

-- Sort by department first, then by salary descending
            SELECT name, department, salary
            FROM employees
            ORDER BY department ASC, salary DESC;

4. Using Column Position

PostgreSQL allows you to sort using the column’s position in the SELECT list.

-- Sort by second column (department)
            SELECT name, department, salary
            FROM employees
            ORDER BY 2 ASC;

5. Sorting with Expressions

You can sort by expressions, functions, or computed columns.

-- Sort employees by uppercase name
            SELECT name, department, salary
            FROM employees
            ORDER BY UPPER(name) ASC;

            -- Sort by length of name
            SELECT name, department, salary
            FROM employees
            ORDER BY LENGTH(name) DESC;

6. Sorting NULL Values

By default, NULL values sort last in ascending order and first in descending order. You can control this using NULLS FIRST or NULLS LAST.

-- Sort by salary ascending, NULLs last
            SELECT name, salary
            FROM employees
            ORDER BY salary ASC NULLS LAST;

            -- Sort by salary descending, NULLs first
            SELECT name, salary
            FROM employees
            ORDER BY salary DESC NULLS FIRST;

7. ORDER BY with WHERE and LIMIT

Sorting is often combined with filtering and limiting results.

-- Top 5 highest paid IT employees
            SELECT name, department, salary
            FROM employees
            WHERE department = 'IT'
            ORDER BY salary DESC
            LIMIT 5;

8. Using ORDER BY in UPDATE and DELETE

PostgreSQL allows ORDER BY in UPDATE and DELETE statements when combined with LIMIT (PostgreSQL 13+). This is useful to control which rows are affected first.

-- Delete 2 lowest paid employees
            DELETE FROM employees
            WHERE department = 'IT'
            ORDER BY salary ASC
            LIMIT 2;

            -- Give raise to 3 highest paid employees
            UPDATE employees
            SET salary = salary * 1.05
            WHERE department = 'IT'
            ORDER BY salary DESC
            LIMIT 3;

9. Best Practices

• Use ORDER BY only when necessary to reduce query execution time.
• Index columns frequently used in sorting to improve performance.
• Use NULLS FIRST or NULLS LAST to handle NULLs explicitly.
• Combine with LIMIT for pagination and top-N queries.

Conclusion

The ORDER BY clause is essential for arranging query results in PostgreSQL. It provides flexible sorting options, including multiple columns, expressions, and NULL handling. Mastering ORDER BY is important for reporting, analytics, and preparing data for further processing. In the next tutorial, we will explore the GROUP BY clause to aggregate data efficiently.