LIMIT and OFFSET

Objectives

By the end of this lesson, you should be able to:

• Cap how many rows a query returns with LIMIT
• Skip rows with OFFSET
• Combine both to page through results

💡 Why this matters: “Show me the top 5 highest earners” or “load 10 results at a time” are both about controlling row count, not filtering by a condition. LIMIT and OFFSET do exactly that.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier in this module.

LIMIT: Capping Row Count

SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 3;

 first_name | salary
------------+---------
 Taylor     | 95000.00
 Priya      | 89000.00
 Erin       | 74000.00

LIMIT 3 returns at most 3 rows. LIMIT almost always pairs with ORDER BY, without a defined order, “the first 3 rows” isn’t a meaningful question, the database is free to return any 3.

OFFSET: Skipping Rows

SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 3;

 first_name | salary
------------+---------
 Alexis     | 71000.00
 Maya       | 67000.00
 Sam        | 63000.00

OFFSET 3 skips the first 3 rows of the (still sorted) result, then LIMIT 3 takes the next 3. Together, this is exactly “page 2” of a 3-per-page listing, the highest 3 earners were page 1 (last example), these are the next 3.

Paging Through Results

This LIMIT/OFFSET pattern is the basis of pagination, common in any application showing a long list a page at a time:

Page 1: LIMIT 3 OFFSET 0
Page 2: LIMIT 3 OFFSET 3
Page 3: LIMIT 3 OFFSET 6

Each page uses the same LIMIT, with OFFSET increasing by that same amount each time, always paired with the same ORDER BY, so the pages stay consistent with each other.

Try It

1. Write a query for the 3 lowest-paid employees.
2. Write a query for the single most recently hired employee (highest hire_date).
3. Using LIMIT and OFFSET, write the query for “page 2” of employees sorted by first_name, 2 per page.
4. Explain, in your own words, why LIMIT without ORDER BY doesn’t reliably answer “give me the top N.”

Recap

• LIMIT n caps a query to at most n rows, almost always paired with ORDER BY to make “top N” meaningful.
• OFFSET n skips the first n rows of the result before LIMIT is applied.
• LIMIT and OFFSET together implement pagination, showing a large result set one page at a time.

Next lesson: this module’s exercises, practicing every filtering, sorting, and limiting technique together.