LIMIT & OFFSET

Real-world tables can contain millions of rows. You rarely want to see all of them at once. LIMIT and OFFSET let you control how many rows are returned and where to start, making them essential for pagination and "top N" queries.

LIMIT: Restricting the Number of Rows

LIMIT caps the number of rows returned:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

This returns only the 5 highest-paid employees. Without LIMIT, you would get every employee.

Important: Always use ORDER BY with LIMIT. Without ordering, the rows you get are unpredictable -- PostgreSQL might return any arbitrary subset of rows.

Top N Queries

The most common use of LIMIT is finding the "top N" items:

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

-- 5 most recent orders
SELECT customer_name, order_date, total_price
FROM orders
ORDER BY order_date DESC
LIMIT 5;

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

OFFSET: Skipping Rows

OFFSET skips a specified number of rows before returning results:

SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 3;

This skips the top 3 earners and returns everyone else, starting from the 4th highest salary.

Combining LIMIT and OFFSET

The real power comes from using both together:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

This skips the first 5 rows and returns the next 5, effectively giving you rows 6 through 10.

Pagination

LIMIT and OFFSET are the foundation of pagination -- showing results one "page" at a time, like search engine results or product listings.

The Pagination Formula

For a given page size and page number:

OFFSET = (page_number - 1) * page_size
LIMIT  = page_size

Example: 10 Items Per Page

-- Page 1 (rows 1-10)
SELECT name, price FROM products
ORDER BY name
LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT name, price FROM products
ORDER BY name
LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT name, price FROM products
ORDER BY name
LIMIT 10 OFFSET 20;

Example: 5 Items Per Page

-- Page 1
SELECT customer_name, order_date, total_price
FROM orders
ORDER BY order_date DESC
LIMIT 5 OFFSET 0;

-- Page 2
SELECT customer_name, order_date, total_price
FROM orders
ORDER BY order_date DESC
LIMIT 5 OFFSET 5;

-- Page 4
SELECT customer_name, order_date, total_price
FROM orders
ORDER BY order_date DESC
LIMIT 5 OFFSET 15;

FETCH FIRST: The SQL Standard Way

LIMIT is a PostgreSQL (and MySQL) extension. The SQL standard uses FETCH FIRST:

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

This is equivalent to LIMIT 5. You can also combine it with OFFSET:

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

FETCH FIRST Variations

-- These are all equivalent:
FETCH FIRST 5 ROWS ONLY
FETCH FIRST 5 ROW ONLY    -- ROW and ROWS are interchangeable
FETCH NEXT 5 ROWS ONLY    -- NEXT and FIRST are interchangeable

FETCH FIRST WITH TIES

WITH TIES includes additional rows that have the same value as the last row in the sort order:

SELECT name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS WITH TIES;

If the 3rd and 4th employees share the same salary, both are included. This can return more than 3 rows, but it ensures fairness when values are tied.

OFFSET with FETCH FIRST

The standard syntax for combining offset with fetch:

SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS
FETCH FIRST 5 ROWS ONLY;

This is the SQL-standard equivalent of LIMIT 5 OFFSET 10.

Performance Considerations

While LIMIT and OFFSET are simple to use, there are performance considerations for large datasets:

Large OFFSET Values Are Slow

-- This is slow on a table with millions of rows
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 1000000;

PostgreSQL must still scan and sort the first 1,000,010 rows before discarding the first million. The further into the dataset you paginate, the slower it gets.

Keyset Pagination (Seek Method)

For large datasets, keyset pagination is more efficient. Instead of using OFFSET, you use a WHERE clause that references the last value from the previous page:

-- Instead of OFFSET, use WHERE to start from where you left off
SELECT name, salary
FROM employees
WHERE salary < 75000
ORDER BY salary DESC
LIMIT 10;

This technique is beyond the scope of this lesson but worth knowing about as your datasets grow.

Common Patterns

Finding the Bottom N

-- 3 cheapest products
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3;

Finding the Nth Row

-- The 3rd highest salary
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

Getting a Sample of Rows

-- Random sample of 5 employees
SELECT name, department
FROM employees
ORDER BY RANDOM()
LIMIT 5;

Note: ORDER BY RANDOM() is fine for small tables but very slow on large ones because it assigns a random number to every row and then sorts.

Try It Yourself

1. Find the 3 most expensive products. Show name and price.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

2. Find the employee with the 2nd highest salary. Show their name and salary.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

3. Write a pagination query to get the 3rd page of orders (page size of 4), sorted by order_date descending. Show customer_name, order_date, and total_price.

SELECT customer_name, order_date, total_price
FROM orders
ORDER BY order_date DESC
LIMIT 4 OFFSET 8;

4. Use FETCH FIRST syntax to get the top 5 employees by salary.

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