PostgreSQL ORDER BY and LIMIT

When querying a table, PostgreSQL does not guarantee the order in which rows are returned. The physical storage order of rows can vary and should never be relied upon. The ORDER BY clause sorts the result set in a defined order. The LIMIT clause restricts how many rows are returned. Together, they are essential for presenting data in a readable, controlled way.

The ORDER BY Clause

Basic Syntax

SELECT column1, column2
FROM table_name
ORDER BY column1;

By default, ORDER BY sorts in ascending order — from the smallest to the largest value, or A to Z for text.

Ascending and Descending Order

-- Ascending (default, low to high, A to Z)
SELECT name, salary FROM employees ORDER BY salary ASC;

-- Descending (high to low, Z to A)
SELECT name, salary FROM employees ORDER BY salary DESC;

The keywords ASC and DESC make the sort direction explicit. Since ASC is the default, it is often omitted, but including it improves readability.

Sorting by Multiple Columns

Multiple sort columns can be specified, separated by commas. PostgreSQL sorts by the first column first, then uses the second column to break ties:

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

Employees are sorted alphabetically by department. Within each department, employees are sorted from highest to lowest salary.

Sorting by Column Position

Columns can also be referenced by their position in the SELECT list (1-based index):

SELECT name, salary FROM employees ORDER BY 2 DESC;

2 refers to the second column in the SELECT list — salary. While this works, it is less readable than using the column name directly and can cause bugs if the SELECT list order changes.

Sorting Text Values

Text is sorted alphabetically by default. PostgreSQL uses the database's collation setting (typically based on locale) to determine sort order:

SELECT name FROM students ORDER BY name ASC;

The result will be alphabetically ordered names from A to Z.

Sorting with NULL Values

By default in PostgreSQL, NULL values appear last in ascending order and first in descending order. This can be overridden:

-- Place NULLs last even in descending order
SELECT name, phone FROM employees ORDER BY phone DESC NULLS LAST;

-- Place NULLs first even in ascending order
SELECT name, phone FROM employees ORDER BY phone ASC NULLS FIRST;

The LIMIT Clause

Basic Syntax

SELECT column1 FROM table_name LIMIT number;

LIMIT restricts the output to the specified number of rows. This is particularly useful when a table has thousands of rows and only a small sample is needed.

Getting the Top N Records

-- Top 3 highest-paid employees
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

ORDER BY combined with LIMIT is the standard way to find the top or bottom N records.

Using LIMIT 1

-- The single highest-paid employee
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1;

This returns only one row — the employee with the highest salary.

The OFFSET Clause

OFFSET skips a specified number of rows before starting to return results. It is typically used together with LIMIT for pagination — displaying data in pages.

-- Page 1: rows 1–5
SELECT name FROM students ORDER BY id LIMIT 5 OFFSET 0;

-- Page 2: rows 6–10
SELECT name FROM students ORDER BY id LIMIT 5 OFFSET 5;

-- Page 3: rows 11–15
SELECT name FROM students ORDER BY id LIMIT 5 OFFSET 10;

The formula for any page is: OFFSET = (page_number - 1) * page_size. For page 3 with 5 rows per page: OFFSET = (3 - 1) * 5 = 10.

FETCH — The SQL Standard Alternative to LIMIT

PostgreSQL also supports the SQL standard FETCH clause, which is equivalent to LIMIT but more portable across different database systems:

SELECT name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

This returns the same result as LIMIT 5. Both are valid in PostgreSQL.

Example: Paginating a Product List

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC(8, 2)
);

INSERT INTO products (name, category, price)
VALUES
    ('Laptop', 'Electronics', 1200.00),
    ('Phone', 'Electronics', 800.00),
    ('Tablet', 'Electronics', 600.00),
    ('Desk', 'Furniture', 350.00),
    ('Chair', 'Furniture', 150.00),
    ('Lamp', 'Furniture', 75.00),
    ('Notebook', 'Stationery', 5.00),
    ('Pen', 'Stationery', 1.50),
    ('Highlighter', 'Stationery', 3.00),
    ('Stapler', 'Stationery', 12.00);

-- All products, cheapest first
SELECT name, price FROM products ORDER BY price ASC;

-- Top 3 most expensive products
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

-- Products sorted by category then by price
SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;

-- Page 2 of results (5 per page)
SELECT name, price
FROM products
ORDER BY id
LIMIT 5 OFFSET 5;

Key Points

• ORDER BY sorts query results in ascending (ASC) or descending (DESC) order.
• Multiple sort columns are allowed; the first column is the primary sort, the second breaks ties.
• NULL values appear last in ASC and first in DESC by default; use NULLS FIRST or NULLS LAST to override.
• LIMIT restricts the number of rows returned.
• OFFSET skips a number of rows before returning results — essential for pagination.
• ORDER BY and LIMIT are almost always used together when finding top or bottom N records.