FETCH FIRST for SQL standard pagination in PostgreSQL

Introduction

FETCH FIRST helps you get a limited number of rows from a big list, making it easier to see just a part of the data at a time.

When you want to show only the top 5 best-selling products from a store.

When you need to display the first 10 recent messages in a chat app.

When you want to preview the first few rows of a large table without loading everything.

When building pages that show data in small chunks, like 20 rows per page.

When you want to limit the number of results returned by a query to save time and resources.

Syntax

PostgreSQL

SELECT column_list
FROM table_name
ORDER BY column_name
FETCH FIRST n ROWS ONLY;

The ORDER BY clause is important to control which rows you get first.

FETCH FIRST n ROWS ONLY limits the output to n rows.

Examples

Gets the top 3 employees with the highest salary.

PostgreSQL

SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

Shows the 5 most recently added products.

PostgreSQL

SELECT name FROM products
ORDER BY created_at DESC
FETCH FIRST 5 ROWS ONLY;

Returns any 10 books from the table (order not guaranteed without ORDER BY).

PostgreSQL

SELECT id, title FROM books
FETCH FIRST 10 ROWS ONLY;

Sample Program

This creates a fruits table, adds 5 fruits with prices, then selects the 3 most expensive fruits.

PostgreSQL

CREATE TABLE fruits (id SERIAL PRIMARY KEY, name VARCHAR(20), price NUMERIC);

INSERT INTO fruits (name, price) VALUES
('Apple', 1.20),
('Banana', 0.50),
('Cherry', 2.00),
('Date', 3.00),
('Elderberry', 1.50);

SELECT name, price FROM fruits
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;

Important Notes

Without ORDER BY, FETCH FIRST returns an arbitrary set of rows, which may vary each time.

FETCH FIRST is part of the SQL standard and works in PostgreSQL 13+ and other modern databases.

You can also use FETCH FIRST n ROWS WITH TIES to include rows that tie on the last value.

Summary

FETCH FIRST limits the number of rows returned by a query.

Always use ORDER BY to control which rows you get first.

Useful for pagination and previewing data.